MySQLで「文字列＋数字」形式のデータを右側の数字順に並べ替える方法

英数字が混在するカラムを並べ替えるには

テーブルにVARCHAR型のカラムがあり、値が「名前＋数字」のように文字列の右側に数字が付いた形式になっている場合を考えてみましょう。例えば、以下のようなデータです。

John1023
Carol9871
David9098

このような場合、カラム全体を右側の数字に基づいて並べ替えたいことがあります。そんなときは、ORDER BY句とRIGHT()関数を組み合わせるのが便利です。

テーブルの作成

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

mysql> create table DemoTable757 (
   Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ClientId varchar(100)
);
Query OK, 0 rows affected (0.53 sec)

レコードの挿入

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

mysql> insert into DemoTable757(ClientId) values('John1023');
Query OK, 1 row affected (0.41 sec)
mysql> insert into DemoTable757(ClientId) values('Carol9871');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable757(ClientId) values('David9098');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable757(ClientId) values('Adam9989');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable757(ClientId) values('Bob9789');
Query OK, 1 row affected (0.20 sec)

登録データの確認

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

mysql> select *from DemoTable757;

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

+----+-----------+
| Id | ClientId  |
+----+-----------+
|  1 | John1023  |
|  2 | Carol9871 |
|  3 | David9098 |
|  4 | Adam9989  |
|  5 | Bob9789   |
+----+-----------+
5 rows in set (0.00 sec)

RIGHT()関数を使った英数字順の並べ替え

MySQLで英数字混在の文字列を、右側の数字順に並べ替えるクエリは以下のとおりです。

mysql> select Id,ClientId from DemoTable757 order by right(ClientId,4);

実行結果：

+----+-----------+
| Id | ClientId  |
+----+-----------+
|  1 | John1023  |
|  3 | David9098 |
|  5 | Bob9789   |
|  2 | Carol9871 |
|  4 | Adam9989  |
+----+-----------+
5 rows in set (0.00 sec)

RIGHT(ClientId, 4) は、ClientId の右側から4文字（ここでは数字部分）を取り出す関数です。この値を基準に並べ替えることで、「1023 → 9098 → 9789 → 9871 → 9989」のように、文字列ではなく数字部分の昇順で正しくソートされていることがわかります。

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

上記の例では数字部分がすべて4桁であるため、RIGHT(ClientId, 4) だけで正しく並べ替えられます。しかし、数字部分の桁数がレコードによって異なる場合は、単純なRIGHT()では意図した順序にならないことがあります。その場合は、LENGTH()と組み合わせる、あるいはSUBSTRING()やREGEXP_REPLACE()などを使って数字部分のみを抽出してから並べ替えるとよいでしょう。