MySQL のデータに対して、SQLで特定の文字列以降の文字を削除する

IT

はじめに

MySQL に存在するデータに対して、SQLで特定の文字列以降の文字を削除する方法をまとめます。
MySQL 5.7 以前をしようしている場合も、本記事の方法を利用することで、任意のキーワード以降の文字列を削除することができます。

特定の文字列以降の文字を削除するSQL

以下の SQL で、特定の文字列以降の文字を削除することができます。

UPDATE {table_name} SET {column_name} = REPLACE({column_name}, SUBSTR({column_name}, INSTR({column_name}, {キーワードの文字列}), CHAR_LENGTH({column_name})), '')

例としてこちらのテーブルを使用します。

mysql> SELECT * FROM dogs;
+------+----------------+---------+-------+
| id   | name           | owner   | birth |
+------+----------------+---------+-------+
|    1 | torapoo (abcd) | mayuko | 8/23  |
|    2 | kachapoo (xxx) | mayuko | 8/23  |
|    3 | dospoo         | mayuko | 8/23  |
+------+----------------+---------+-------+

name列の”( )”の文字列を削除します。
” (“をキーワードとし、以下のSQLを実行します。

UPDATE dogs SET name = REPLACE(name, SUBSTR(name, INSTR(name, ' ('), CHAR_LENGTH(name)), '');

まるかっこと、まるかっこの前にあったスペースが削除されていることが確認できます。

mysql> SELECT * FROM dogs;
+------+----------+---------+-------+
| id   | name     | owner   | birth |
+------+----------+---------+-------+
|    1 | torapoo  | masaki | 8/23  |
|    2 | kachapoo | masaki | 8/23  |
|    3 | dospoo   | masaki | 8/23  |
+------+----------+---------+-------+

REGEXP_REPLACE（ MySQL 8.0 以降のみ）

MySQL 8.0 以降を使用している場合は、REGEXP_REPLACE()を利用しても同様のことを行うことができます。REGEXP_REPLACE()の使用方法は次の通りです。

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

REGEXP_REPLACE()を使用した方法も試してみます。下記のSQLを実行します。

UPDATE dogs SET name = REGEXP_REPLACE(name, '[ (].*', '');

先ほどと同様に、まるかっことまるかっこの前にあったスペースが削除されていることが確認できます。

mysql> SELECT * FROM dogs;
+------+----------+---------+-------+
| id   | name     | owner   | birth |
+------+----------+---------+-------+
|    1 | torapoo  | masaki | 8/23  |
|    2 | kachapoo | masaki | 8/23  |
|    3 | dospoo   | masaki | 8/23  |
+------+----------+---------+-------+

使用できる正規表現の詳細は公式ドキュメントを参照してください。

正規表現説明
.任意の1文字に一致
[…]括弧内のいずれかの文字に一致
– [abc] なら a b c のいずれかに一致
– [a-z] なら a から z のいずれかに一致
– [^a-z] なら a から z 以外に一致
*直前の文字の 0 回以上の繰り返しに一致
+直前の文字の 1 回以上の繰り返しに一致
?直前の文字の 0 回か 1 回に一致
xx|yyxx または yy の文字列に一致
^文字列の先頭に一致
$文字列の末尾に一致
(xyz)かたまりを表現

おわりに

MySQL に存在するデータに対して、SQLで特定の文字列以降の文字を削除する方法をまとめました。MySQL 8.0 以降を使用している場合はREGEXP_REPLACE()を使用することで、正規表現で置換することができました。MySQL 5.7 以前でも本記事で紹介した SQL によって定の文字列以降の文字を削除することができました。この記事がどなたかの参考になれば幸いです。

参考

コメント