MySQLで文字列フィールドの数値部分を抽出して行をグループ化する方法

MySQLでは、文字列型のフィールドに「+」演算子を使って数値の0を加算することで、文字列の先頭にある数字部分だけを数値として取り出すことができます。例えば、「9844Bob」のような文字列から、数値部分である「9844」を取得したいケースがこれに該当します。

サンプルテーブルの作成

まずはテーブルを作成しましょう。

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

テストデータの挿入

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

mysql> insert into DemoTable values('9844Bob');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('6375DavidMiller');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values('007');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable values('97474BobBrown');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('9844Bob56Taylor');
Query OK, 1 row affected (0.16 sec)

登録データの確認

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

mysql> select *from DemoTable;

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

+-----------------+
| StudentId       |
+-----------------+
| 9844Bob         |
| 6375DavidMiller |
| 007             |
| 97474BobBrown   |
| 9844Bob56Taylor |
+-----------------+
5 rows in set (0.00 sec)

文字列内の数値でグループ化するクエリ

文字列フィールドの先頭の数値部分をもとに行をグループ化するには、次のように「0 + 文字列カラム」を指定します。MySQLは暗黙的に文字列を数値へ変換するため、先頭の連続した数字だけが数値として評価されます。

mysql> select StudentId,0+StudentId as GroupValue from DemoTable;

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

+-----------------+------------+
| StudentId       | GroupValue |
+-----------------+------------+
| 9844Bob         |       9844 |
| 6375DavidMiller |       6375 |
| 007             |          7 |
| 97474BobBrown   |      97474 |
| 9844Bob56Taylor |       9844 |
+-----------------+------------+
5 rows in set, 4 warnings (0.00 sec)

ポイント解説

• 「007」→「7」: 先頭のゼロは数値変換時に無視されるため、7として扱われます。
• 「9844Bob」と「9844Bob56Taylor」: どちらも先頭の数値「9844」が抽出されるため、同じグループとしてまとめられます。
• warningsについて: 文字列から数値への暗黙的な変換が行われるため警告が表示されますが、動作自体には問題ありません。

この手法をGROUP BY句と組み合わせれば、「SELECT 0+StudentId AS GroupValue, COUNT(*) FROM DemoTable GROUP BY GroupValue;」のように、数値部分ごとの件数集計などにも応用できます。