MySQLで文字列を解析し、アンダースコア区切りの大きな文字列から数値を抽出する方法

はじめに

MySQLでは、CAST()関数とREPLACE()関数を組み合わせることで、アンダースコアやハイフンで区切られた長い文字列から、不要な文字部分を取り除き、数値だけを抽出することができます。この記事では、具体的なサンプルを使ってその手順を解説します。

サンプルテーブルの作成

まず、以下のようにテーブルを作成します。

mysql> create table DemoTable1961
(   Title text
);
Query OK, 0 rows affected (0.00 sec)

レコードの挿入

insertコマンドを使って、テーブルにレコードを挿入します。

mysql> insert into DemoTable1961 values('You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts');
Query OK, 1 row affected (0.00 sec)

レコードの表示

select文を使って、テーブル内のすべてのレコードを表示してみましょう。

mysql> select * from DemoTable1961;

実行すると、次のような出力が得られます。

+------------------------------------------------------------------------------------+
| Title                                                                   |
+------------------------------------------------------------------------------------+
| You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

CAST()とREPLACE()で数値を抽出するクエリ

ここからが本題です。CAST()とREPLACE()を使って、文字列から数値部分のみを取り出すクエリは以下の通りです。

mysql> select cast(replace(replace('You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts','You_can_remove_the_string_part_only-',''),
   '-But_You_can_not_remove_the_numeric_parts','') as unsigned) as Output from DemoTable1961;

このクエリの仕組みを簡単に説明します。まず内側のREPLACE()で、数値より前の文字列「You_can_remove_the_string_part_only-」を削除します。次に外側のREPLACE()で、数値より後ろの文字列「-But_You_can_not_remove_the_numeric_parts」を削除します。最後にCAST()で残った文字列「10001」を符号なし整数（UNSIGNED）に変換しています。

実行結果

上記のクエリを実行すると、次のような出力が得られます。

+--------+
| Output |
+--------+
|  10001 |
+--------+
1 row in set (0.00 sec)

まとめ

このように、REPLACE()関数で不要な文字列部分を段階的に削除し、CAST()関数で数値型に変換することで、複雑な区切り文字を含む文字列からでも目的の数値を簡単に抽出できます。SUBSTRING_INDEX()などを併用すれば、より柔軟な解析も可能になるので、状況に応じて使い分けるとよいでしょう。