MySQLの列から括弧で囲まれた末尾の数字を削除する方法

MySQLの列に格納された文字列から、「(7)」のように括弧で囲まれた末尾の数字を削除したいケースはよくあります。これには、trim()関数とsubstring()関数を組み合わせて使用します。本記事では、実際のサンプルコードを交えながら手順を詳しく解説します。

1. サンプルテーブルの作成

まず、動作確認用のテーブルを作成しましょう。

mysql> create table DemoTable
(
    Name varchar(100)
);
Query OK, 0 rows affected (0.80 sec)

2. レコードの挿入

insertコマンドを使用して、テーブルにいくつかのレコードを挿入します。

mysql> insert into DemoTable values('1stJohn');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('1stJohn (7)');
Query OK, 1 row affected (0.65 sec)
mysql> insert into DemoTable values('2ndSam');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('2ndSam (4)');
Query OK, 1 row affected (0.15 sec)

3. 挿入したデータの確認

select文を実行して、テーブル内のすべてのレコードを表示してみます。

mysql> select *from DemoTable;

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

+-------------+
| Name        |
+-------------+
| 1stJohn     |
| 1stJohn (7) |
| 2ndSam      |
| 2ndSam (4)  |
+-------------+
4 rows in set (0.00 sec)

4. 括弧で囲まれた末尾の数字を削除するクエリ

MySQLの列から、括弧で囲まれた末尾の数字を削除するには、次のクエリを実行します。

mysql> select trim(substring(Name, 1, (CHAR_LENGTH(Name) - LOCATE('(', REVERSE(Name))))) AS RemovingTrailingNumbers from DemoTable;

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

+-------------------------+
| RemovingTrailingNumbers |
+-------------------------+
| 1stJohn                 |
| 1stJohn                 |
| 2ndSam                  |
| 2ndSam                  |
+-------------------------+
4 rows in set (0.00 sec)

クエリの仕組み

このクエリでは、複数の関数を組み合わせて目的の処理を実現しています。それぞれの役割は以下の通りです。

• REVERSE(Name): 文字列を反転させます。例えば「1stJohn (7)」は「)7( nhoJts1」になります。
• LOCATE('(', REVERSE(Name)): 反転した文字列の中で「(」が最初に現れる位置を検索します。これは元の文字列における閉じ括弧「)」までの距離に相当します。
• CHAR_LENGTH(Name) - LOCATE(...): 文字列全体の長さから括弧部分の長さを差し引き、残すべき部分の文字数を算出します。
• substring(Name, 1, ...): 先頭1文字目から、算出した文字数分の文字列を抽出します。
• trim(): 抽出後の文字列に残った余分な空白を取り除きます。

このようにして、「1stJohn (7)」のようなデータから括弧部分を除去し、「1stJohn」というすっきりした値だけを取り出すことができます。名前データの重複チェックや集計処理など、さまざまな場面で活用できるテクニックなので、ぜひ覚えておきましょう。