MySQLで文字列から数字部分を抽出する方法をわかりやすく解説

はじめに

MySQLでは、「John19383」のような文字列から「19383」といった数字部分だけを抽出したい場面があります。本記事では、REVERSE関数・FORMAT関数・REPLACE関数を組み合わせて数字部分を抽出する方法を、実際のSQL例とともにわかりやすく解説します。

サンプルテーブルの作成

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

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

レコードの挿入

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

mysql> insert into DemoTable values('John19383');
Query OK, 1 row affected (0.18 sec)

mysql> insert into DemoTable values('Carol9999');
Query OK, 1 row affected (0.16 sec)

mysql> insert into DemoTable values('David123456');
Query OK, 1 row affected (0.15 sec)

登録データの確認

SELECT文でテーブル内のすべてのレコードを表示します。

mysql> select *from DemoTable;

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

+-------------+
| StudentId   |
+-------------+
| John19383   |
| Carol9999   |
| David123456 |
+-------------+
3 rows in set (0.00 sec)

文字列から数字部分を抽出するクエリ

文字列から数字部分を抽出するには、次のクエリを実行します。

mysql> SELECT replace(reverse(FORMAT(reverse(StudentId), 0)), ',', '') as OnlyDigit from DemoTable;

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

+-----------+
| OnlyDigit |
+-----------+
| 19383     |
| 9999      |
| 123456    |
+-----------+
3 rows in set (0.05 sec)

クエリの仕組みを解説

一見複雑に見えるこのクエリも、処理の流れを順に追えば理解しやすくなります。ここでは「John19383」を例に説明します。

1. REVERSE(StudentId) ― 文字列を逆順に並べ替えます。「John19383」は「38391nhoJ」となり、数字部分が先頭に移動します。
2. FORMAT(..., 0) ― FORMAT関数は値を数値として評価し、カンマ区切りの文字列に整形します。第2引数に0を指定すると小数部分は切り捨てられます。先頭の「38391」だけが数値として認識され、結果は「38,391」になります。
3. REVERSE(...) ― 再び逆順にすることで、元の数字の並びに戻ります。この時点で「19,383」という文字列になっています。
4. REPLACE(..., ',', '') ― 最後にREPLACE関数でカンマを削除すれば、純粋な数字列「19383」が完成します。

補足: MySQL 8.0以降ならREGEXP_REPLACEも使える

MySQL 8.0以降を使用している場合は、正規表現を利用できるREGEXP_REPLACE関数の方がより直感的に記述できます。

mysql> SELECT REGEXP_REPLACE(StudentId, '[^0-9]', '') as OnlyDigit from DemoTable;

このクエリは、数字以外のすべての文字を空文字に置き換えることで、数字部分だけを抽出します。可読性が高いため、環境が許すのであればこちらの方法を推奨します。

まとめ

MySQLで文字列から数字部分を抽出するには、REVERSE・FORMAT・REPLACEの3つの関数を組み合わせる方法が有効です。MySQL 8.0以降の環境であれば、REGEXP_REPLACEを使うことでよりシンプルに実現できます。用途やサーバーのバージョンに応じて、最適な方法を選択してください。