MySQLで「John_120」のような列の一部（数値部分）を基準にソートする方法

はじめに

「John_120」のように「名前_数値」という形式で保存されたデータを、アンダースコア（_）以降の数値部分を基準にソートしたいケースはよくあります。そんなときは、SUBSTRING_INDEX()関数とORDER BY句を組み合わせることで簡単に実現できます。ここでは、テーブル作成から実際のソートまでの手順を具体的な例とともに解説します。

テーブルの作成

まず、サンプル用のテーブルを作成しましょう。

mysql> create table DemoTable1502
    -> (
    -> StudentId varchar(40)
    -> );
Query OK, 0 rows affected (0.54 sec)

レコードの挿入

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

mysql> insert into DemoTable1502 values('John_120');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1502 values('John_201');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1502 values('Mike_178');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1502 values('Bob_198');
Query OK, 1 row affected (0.36 sec)

登録データの確認

select文を実行して、テーブル内のすべてのレコードを表示してみます。

mysql> select * from DemoTable1502;

以下のような出力が得られます。

+-----------+
| StudentId |
+-----------+
| John_120  |
| John_201  |
| Mike_178  |
| Bob_198   |
+-----------+
4 rows in set (0.00 sec)

列の部分的な値でソートするクエリ

次のクエリでは、SUBSTRING_INDEX()を使って「_」を区切り文字として文字列を左右に分割し、右側の数値部分（RightPart）を基準に昇順ソートを行っています。

mysql> select substring_index(StudentId,'_',1) as LeftPart,
    -> substring_index(StudentId,'_',-1) as RightPart
    -> from DemoTable1502
    -> order by RightPart;

実行すると、数値部分を基準に並べ替えられた以下の結果が得られます。

+----------+-----------+
| LeftPart | RightPart |
+----------+-----------+
| John     |       120 |
| Mike     |       178 |
| Bob      |       198 |
| John     |       201 |
+----------+-----------+
4 rows in set (0.00 sec)

ポイント解説

substring_index(StudentId,'_',1)は区切り文字「_」より左側の文字列を取得し、substring_index(StudentId,'_',-1)は右側（末尾から1番目）の文字列を取得します。負の値を指定すると右から数えた位置の文字列が返されるのがポイントです。このように分割した値に対してORDER BYを適用することで、元の文字列の一部分だけを基準とした柔軟なソートが可能になります。名前とIDが混在するデータの整理など、実務でも幅広く活用できるテクニックです。