MySQLのテーブルレコードで最初に出現する文字だけを検索・置換する方法

MySQLで最初の出現箇所のみを検索・置換する方法

MySQLのテーブルレコード内で、特定の文字が最初に出現した箇所だけを別の文字に置き換えたい場合は、CONCAT()関数とREPLACE()関数を組み合わせることで実現できます。さらに、最初の出現位置を特定するためにINSTR()関数を使用します。

基本的な構文

UPDATE yourTableName
SET UserPost = CONCAT(
    REPLACE(LEFT(yourColumnName, INSTR(yourColumnName, 'k')), 'k', 'i'),
    SUBSTRING(yourColumnName, INSTR(yourColumnName, 'k') + 1)
);

このクエリの動作を分解すると、次のようになります。

• INSTR()：対象の文字が最初に出現する位置を返します。
• LEFT()：文字列の先頭から、INSTR()で取得した位置までの部分を切り出します。
• REPLACE()：切り出した部分に含まれる対象文字を新しい文字に置換します。
• SUBSTRING()：最初の出現位置より後ろの残りの文字列を取り出し、CONCAT()で置換済みの部分と連結します。

サンプルテーブルの作成

実際に動作を確認するため、まずテーブルを作成しましょう。

mysql> CREATE TABLE UserInformation
    -> (
    -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> UserName varchar(10),
    -> UserPost text
    -> );
Query OK, 0 rows affected (2.05 sec)

テストデータの挿入

次に、INSERTコマンドを使っていくつかのレコードを挿入します。

mysql> INSERT INTO UserInformation(UserName,UserPost) VALUES('Larry','Thks is a MySQL query');
Query OK, 1 row affected (0.16 sec)
mysql> INSERT INTO UserInformation(UserName,UserPost) VALUES('Mike','Thks is not a java program');
Query OK, 1 row affected (0.31 sec)
mysql> INSERT INTO UserInformation(UserName,UserPost) VALUES('Sam','Thks is a SQL syntax');
Query OK, 1 row affected (0.18 sec)

登録データの確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> SELECT *FROM UserInformation;

実行結果は以下の通りです。

+--------+----------+----------------------------+
| UserId | UserName | UserPost                   |
+--------+----------+----------------------------+
|      1 | Larry    | Thks is a MySQL query      |
|      2 | Mike     | Thks is not a java program |
|      3 | Sam      | Thks is a SQL syntax       |
+--------+----------+----------------------------+
3 rows in set (0.00 sec)

最初の出現箇所だけを置換するUPDATE文

それでは、レコード内で最初に出現する「k」だけを「i」に置き換えるクエリを実行します。

mysql> UPDATE UserInformation
    -> SET UserPost=CONCAT(REPLACE(LEFT(UserPost, INSTR(UserPost, 'k')), 'k', 'i'),
    -> SUBSTRING(UserPost, INSTR(UserPost, 'k') + 1));
Query OK, 3 rows affected (0.16 sec)
Rows matched: 3 Changed: 3 Warnings: 0

置換後の結果確認

もう一度すべてのレコードを表示して、結果を確認しましょう。

mysql> SELECT *FROM UserInformation;

実行結果を見ると、各レコードで最初に出現した「k」だけが「i」に置き換わり、「Thks」が「This」になっていることがわかります。

+--------+----------+----------------------------+
| UserId | UserName | UserPost                   |
+--------+----------+----------------------------+
|      1 | Larry    | This is a MySQL query      |
|      2 | Mike     | This is not a java program |
|      3 | Sam      | This is a SQL syntax       |
+--------+----------+----------------------------+
3 rows in set (0.00 sec)

まとめ

REPLACE()関数を単独で使うと文字列内のすべての出現箇所が置き換えられますが、INSTR()とLEFT()／SUBSTRING()を組み合わせることで、「最初の1箇所だけ」を対象とした置換が可能になります。データクレンジングや一括修正の際に非常に便利なテクニックです。なお、対象の文字がレコード内に存在しない場合、INSTR()は0を返すため、意図しない動作を防ぎたい場合はWHERE句で条件を絞り込んでおくとより安全です。