MySQLのレコードから数字を抽出する方法：CONVERT関数の実践的な使い方

MySQLでレコードから数字を抽出する方法

「John-11」のような文字列が保存されたカラムから、数字の部分だけを取り出したいケースはよくあります。そんなときは CONVERT() 関数や正規表現を活用しましょう。CONVERT() は値をあるデータ型から別のデータ型へ変換するための関数で、これを利用することで文字列中の数字部分を数値として取得できます。

本記事では、テーブルの作成から数字の抽出・並べ替えまでを、実際のSQL例とともに順を追って解説します。

ステップ1：テーブルを作成する

まずはサンプル用のテーブルを作成します。

mysql> create table textIntoNumberDemo
    -> (
    -> Name varchar(100)
    -> );
Query OK, 0 rows affected (0.47 sec)

ステップ2：レコードを挿入する

続いて、「ハイフン＋数字」という形式のサンプルデータをいくつか登録します。

mysql> insert into textIntoNumberDemo values('John-11');
Query OK, 1 row affected (0.11 sec)

mysql> insert into textIntoNumberDemo values('John-12');
Query OK, 1 row affected (0.17 sec)

mysql> insert into textIntoNumberDemo values('John-2');
Query OK, 1 row affected (0.11 sec)

mysql> insert into textIntoNumberDemo values('John-4');
Query OK, 1 row affected (0.14 sec)

ステップ3：登録済みレコードを確認する

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

mysql> select *from textIntoNumberDemo;

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

+---------+
| Name    |
+---------+
| John-11 |
| John-12 |
| John-2  |
| John-4  |
+---------+
4 rows in set (0.00 sec)

ステップ4：数字を抽出してソートする

数字部分を取り出すための基本構文は次のとおりです。

SELECT yourColumnName,CONVERT(SUBSTRING_INDEX(yourColumnName,'-',-1),UNSIGNED INTEGER) AS yourVariableName
FROM yourTableName
order by yourVariableName;

ポイントは、SUBSTRING_INDEX(カラム名, '-', -1) によってハイフンより後ろの文字列（＝数字部分）を切り出し、それを CONVERT(... , UNSIGNED INTEGER) で符号なし整数に変換しているところです。こうすることで、文字列としてではなく数値としての大小順でソートできるようになります。

では、実際にクエリを実行してみましょう。

mysql> SELECT Name,CONVERT(SUBSTRING_INDEX(Name,'-',-1),UNSIGNED INTEGER) AS MyNumber
    -> FROM textIntoNumberDemo
    -> order by MyNumber;

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

+---------+----------+
| Name    | MyNumber |
+---------+----------+
| John-2  |        2 |
| John-4  |        4 |
| John-11 |       11 |
| John-12 |       12 |
+---------+----------+
4 rows in set (0.00 sec)

「John-2」「John-4」「John-11」「John-12」が、辞書順ではなく数値の昇順（2 → 4 → 11 → 12）で正しく並んでいることが分かります。単純に ORDER BY Name とした場合、「John-11」が「John-2」よりも先に表示されてしまうため、この手法がとても役立ちます。

補足：文字列中に埋め込まれた数字を取り出す場合

ハイフン区切りのような決まった形式ではなく、文字列のあちこちに数字が含まれる場合は、MySQL 8.0 以降で使える REGEXP_REPLACE() を組み合わせるのが便利です。

SELECT REGEXP_REPLACE(Name, '[^0-9]', '') AS ExtractedNumber
FROM textIntoNumberDemo;

このクエリは、半角数字以外の文字をすべて除去し、残った数字のみを返します。位置が不定のデータから数字だけを抜き出したいときに有効な方法です。