MySQLで英数字文字列から数字のみを基準に並べ替える方法

MySQLで英数字文字列から数字のみを基準に並べ替える方法

「Adam_101」や「121John_567」のような英数字が混在した文字列から、数字の部分だけを基準にレコードを並べ替えたいケースはよくあります。MySQLでは、ORDER BY句とRIGHT()関数を組み合わせることで、このような並べ替えを簡単に実現できます。

RIGHT()関数は、文字列の右端から指定した文字数を抽出する関数です。例えば「RIGHT(StudentCode, 3)」と記述すると、StudentCode列の値の末尾3文字が取得されます。これを並べ替えのキーとして指定することで、文字列中の数字部分を基準にソートが可能になります。

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

まず、以下のSQL文でテーブルを作成します。

mysql> create table DemoTable1948
    (
    StudentCode varchar(20)
    );
Query OK, 0 rows affected (0.00 sec)

2. レコードの挿入

次に、INSERT文を使ってサンプルデータを挿入します。

mysql> insert into DemoTable1948 values('121John_567');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1948 values('Adam_101');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1948 values('Bob_563');
Query OK, 1 row affected (0.00 sec)
mysql> insert into DemoTable1948 values('Sam_346');
Query OK, 1 row affected (0.00 sec)

3. テーブルの内容を確認

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

mysql> select * from DemoTable1948;

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

+-------------+
| StudentCode |
+-------------+
| 121John_567 |
| Adam_101    |
| Bob_563     |
| Sam_346     |
+-------------+
4 rows in set (0.00 sec)

4. 数字部分を基準に並べ替えるクエリ

以下のクエリでは、RIGHT()関数で末尾3文字（数字部分）を抽出し、それをORDER BY句に指定することで並べ替えを行っています。

mysql> select * from DemoTable1948
    order by RIGHT(StudentCode,3);

実行結果は以下の通りです。末尾の数字の昇順に正しくソートされていることが確認できます。

+-------------+
| StudentCode |
+-------------+
| Adam_101    |
| Sam_346     |
| Bob_563     |
| 121John_567 |
+-------------+
4 rows in set (0.00 sec)

補足：数字の桁数が異なる場合の注意点

RIGHT()関数による比較は文字列として行われるため、数字の桁数がレコードごとに異なる場合は、意図した順序にならないことがあります。そのような場合は、CAST()関数を組み合わせて数値型に変換してから並べ替えると安全です。

mysql> select * from DemoTable1948
    order by CAST(RIGHT(StudentCode,3) AS UNSIGNED);

このように、RIGHT()関数とORDER BY句を活用すれば、英数字混在の文字列から数字部分だけを取り出して柔軟にソートできます。