MySQL で改行を削除する方法
データベースに保存されたテキストデータの中に、意図しない改行が入っていて困ったことはありませんか?
この記事では、MySQL で 文字列中の改行を削除する方法と、その際の注意点についてご紹介します。
改行を削除する基本的な方法
改行は、テキストの中で行を分けるための特殊文字で、OS によって使われる文字が異なります。
- Unix/Linux/macOS は \n - (LF)
- Windows は \r\n - (CR+LF)
データに予期せぬ改行が入っていると、検索時やシステム間でデータをやりとりするような時など、影響が出ることがあります。
MySQL で改行を削除するシンプルな方法は、REPLACE() 関数を使って改行を空の文字に置き換えてしまうことです。
例えば、school_db データベースの students テーブルの \n の改行が入っているレコードがあったとします。
SELECT *
FROM students
WHERE last_name LIKE '%\n%';
見た目ではわかりにくいですが、student_id = 1 の last_name は Tana\nka、4 の last_name には Suzuki\n というデータが入っています。
この last_name から改行 \n を取り除くには、次のようにできます。
UPDATE students
SET last_name = REPLACE(last_name, '\n', '')
WHERE last_name LIKE '%\n%';
[実行結果]
もう一度、先ほどの SELECT 文を実行すると、改行が削除されていることがわかります。
REPLACE() と CHAR() 関数を使って改行を削除する
REPLACE() 関数 に加えて、CHAR() 関数を使って改行文字を指定して、取り除くこともできます。
\n (Line Feed: LF) は CHAR(10) で、\r (Carriage Return: CR) が CHAR(13) です。
今回のように、\n (Line Feed: LF) が入っていることがわかっている場合は、次のようにしても削除できます。
last_name を \n 入りのデータに戻して、以下のクエリーを実行してみます。
SELECT *
FROM students
WHERE last_name LIKE CONCAT('%', CHAR(10), '%');
UPDATE students
SET last_name = REPLACE(last_name, CHAR(10), '')
WHERE last_name LIKE CONCAT('%', CHAR(10), '%');
[実行結果]
もう一度、先ほどの SELECT 文を実行すると、改行が削除されていることがわかります。
複数の改行コードをまとめて削除する
Windows から取り込んだデータには \r\n の改行が含まれている可能性があります。
どちらの改行が含まれているかわからない状態で、改行を削除したいケースがあるかもしれません。
今回は、school_db データベースの students テーブルの last_name カラムに \n と \r\n の改行が入っているレコードがあったとします。
改行の入っているレコードは、次のクエリーで確認できます。
SELECT *
FROM students
WHERE last_name LIKE CONCAT('%', CHAR(10), '%')
OR last_name LIKE CONCAT('%', CHAR(13), '%');
見た目ではわかりにくいですが、student_id = 1 の last_name は Tana\nka、4 の last_name には Suzuki\n, 6 の last_name には Saito\r\n いうデータが入っています。
ここで、どちらの改行が含まれているかわからない状態で、改行を削除したい時は、次のようにできます。
UPDATE students
SET last_name = REPLACE(REPLACE(last_name, CHAR(13), ''), CHAR(10), '')
WHERE last_name LIKE CONCAT('%', CHAR(10), '%')
OR last_name LIKE CONCAT('%', CHAR(13), '%');
CHAR(13) を空の文字列に置き換えてから、CHAR(10) も空の文字列に置き換えています。
[実行結果]
もう一度、先ほどの SELECT 文を実行すると、\n も \r\n も削除されていることがわかります。
文字列の最後にある改行コードのみを削除する
文字列中にある改行には意味があって削除したくなく、文字列の最後に改行がある場合のみ削除したいケースもあるかもしれません。
MySQL 8.0 以上であれば、正規表現で文字列末尾の改行を簡単に削除できます。
先ほどと同様に、school_db データベースの students テーブルの student_id = 1 の last_name は Tana\nka、4 の last_name には Suzuki\n, 6 の last_name には Saito\r\n いうデータを保存してあります。
まずは、SELECT 文で、文字列の末尾に改行があるデータを取得してみましょう。
SELECT *
FROM students
WHERE last_name REGEXP '[\r\n]$';
[実行結果]
WHERE 句で REGEXP をつかって、last_name の値が正規表現 [\r\n]$ にマッチするレコードを取得しています。
正規表現 [\r\n]$ は「文字列の末尾が \r または \n のどちらか1文字で終わっている場合にマッチする」という意味です。
データが改行コードで終わる、student_id = 4 と 6 のデータが取得できていますね。
そして、文字列の最後にある改行コードのみを削除するには、REGEXP_REPLACE() 関数を使って、次のようにできます。
UPDATE students
SET last_name = REGEXP_REPLACE(last_name, '[\r\n]+$', '')
WHERE last_name REGEXP '[\r\n]$';
[実行結果]
REGEXP_REPLACE() は、正規表現にマッチする箇所を、指定した文字列に置き換えてくれる関数です。
ここでは、[\r\n]+$ にマッチする箇所を、空の文字列に置き換えています。
正規表現 [\r\n]+$ は「文字列の末尾にある 1 文字以上の改行コード(\r , \n, またはその組み合わせ)」 にマッチする」という意味です。
2 行のデータが更新され、もう一度、先ほどの SELECT 文を実行すると、末尾の改行が取り除かれていることがわかります。
改行を削除する際、特に大量のデータを UPDATE する前には、データのバックアップをとってから実行してくださいね。
以上、MySQL で改行を削除する方法と、それに関連する注意点についてご説明しました。