SQLで改行コードやタブコードを削除(置換)する方法

2017.12.17 2024.08.20

SQLで改行コードやタブコードを削除する方法です。

SQLで改行コードやタブコードを削除(置換)する方法

利用者の目に見えない改行コードやタブコードがテーブル内に存在すると、アプリケーションによからぬ影響を及ぼす場合があります。もちろんそうならないように、様々なチェックを実装しなければならないのですが、入ってしまったら仕方ない。改行コードやタブコードを削除するしかありません。

ここでは SQLで改行コードやタブコードを削除する方法 を紹介します。

1 環境
2 SQLで改行コードを削除する方法
3 SQLでタブコードを削除する方法
4 正規表現を使って削除する
5 参考サイト
6 まとめ

環境

ORALCE 11g

ここでは Oracle を使っていますが、データベースはなんでもいいです。SQL の構文には注意してください。

SQLで改行コードを削除する方法

まずは改行コードを理解しましょう。

SQL で改行コードを指定する際には CHAR 関数を使います。

CHAR(13) - CR: キャリッジリターン (Carriage Return)
CHAR(10) - LF: ラインフィード (Line Feed)

Oracle の場合は CHR(13)、CHR(10) となります。

改行コードは OS やテキストエディタによって LF だったり CR+LF だったりしますので注意が必要です。

Oracle上に、こんなテーブルとデータがあったとします。


CREATE TABLE EXAMPLES (
  REMARKS  NVARCHAR2(1000)
);
INSERT INTO EXAMPLES VALUES ('改行コードは'||CHR(10)||'LFです。');
INSERT INTO EXAMPLES VALUES ('改行コードは'||CHR(13)||CHR(10)||'CR+LFです。');
INSERT INTO EXAMPLES VALUES ('改行コードは'||CHR(13)||'CRです。');
INSERT INTO EXAMPLES VALUES ('改行コードはありません。');

まずは、検索して対象を見つけましょう。


SELECT * FROM EXAMPLES
WHERE REMARKS LIKE '%'||CHR(13)||'%' OR REMARKS LIKE '%'||CHR(10)||'%';

件数と状態を把握したら削除してしまいましょう。

削除するには REPLACE 関数を利用します。


UPDATE EXAMPLES SET
REMARKS = REPLACE(REPLACE(REMARKS, CHR(13), null), CHR(10),null)
WHERE REMARKS LIKE '%'||CHR(13)||'%' OR REMARKS LIKE '%'||CHR(10)||'%';

再検索してデータが取得できなければ削除できた証拠です。

SQLでタブコードを削除する方法

タブコードも改行コードと同じで CHAR 関数を利用します。

CHAR(9) - HT: 水平タブ

Oracle上に、こんなデータがあったとします。


INSERT INTO EXAMPLES VALUES ('タブコードは'||CHR(9)||'HTです。');
INSERT INTO EXAMPLES VALUES ('タブコードはありません。');

同じ要領で削除します。


UPDATE EXAMPLES SET
REMARKS = REPLACE(REMARKS, CHR(9), null)
WHERE REMARKS LIKE '%'||CHR(9)||'%';

検索してデータが取得できなければ削除できた証拠です。

正規表現を使って削除する

正規表現を使えば、不要な文字コードをまとめて削除できます。

[[:cntrl:]] - POSIX正規表現: 制御文字全部
[[:blank:]] - POSIX正規表現: スペースとタブ
[[:space:]] - POSIX正規表現: スペースとタブと改ページ

たとえばこんなデータがあったとします。


INSERT INTO EXAMPLES VALUES ('いろんなASCIIコード '||CHR(9)||CHR(10)||CHR(13)||'　混じってます。');

こんな UPDATE 文で更新できます。


UPDATE EXAMPLES SET
REMARKS = regexp_replace(REMARKS,'[[:cntrl:]]', null)

タブと改ページが削除されていれば OK です。

参考サイト

・データベース・アプリケーションでの正規表現の使用

・どのUNIXコマンドでも使える正規表現 - Qiita

まとめ

いつの間にやらテーブルに保存されている制御文字・・・。気が付くのはトラブルが発生した時だったなんてのは、よく聞く話ですね。各フィールドに保存できる文字や記号はきちんと決めておくべきだろうと思いますが、なかなかどうして決まっていないことの方が多いですね。

くれぐれも改行コードやタブコードを削除(置換)する場合にはバックアップを取ってから実行してくださいね。うっかり違うフィールドから消えてしまった・・・なんて二次災害がないように。

おつかれさまでした。