MySQLで英数字カラムを正しく並べ替える方法｜LIKE演算子とSUBSTRING()の活用術

「S/TU/10」「S/TU/100」「S/TU/1000」のように英字と数字が混在したデータは、通常のORDER BYでは文字列として比較されるため、数値の大小順どおりに並びません。そこで役立つのが、LIKE演算子とSUBSTRING()関数を組み合わせたソート方法です。本記事では、具体的な手順をサンプルコード付きで解説します。

1. サンプルテーブルを作成する

まず、学籍IDを格納するテーブルを作成しましょう。

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

2. レコードを挿入する

insertコマンドを使って、いくつかのレコードを追加します。

mysql> insert into DemoTable values('S/TU/100');
Query OK, 1 row affected (0.28 sec)

mysql> insert into DemoTable values('S/TU/1000');
Query OK, 1 row affected (0.22 sec)

mysql> insert into DemoTable values('S/TU/10');
Query OK, 1 row affected (0.47 sec)

3. 登録データを確認する

select文でテーブル内の全レコードを表示してみます。

mysql> select *from DemoTable;

出力結果

+-----------+
| StudentId |
+-----------+
| S/TU/100  |
| S/TU/1000 |
| S/TU/10   |
+-----------+
3 rows in set (0.00 sec)

ご覧のとおり、そのままでは辞書順（文字列比較）で並ぶため、「S/TU/10」が最後に表示され、数値としては不自然な順序になってしまいます。

4. 英数字カラムをソートするクエリ

MySQLで英数字の列を自然な順序で並べ替えるには、次のようなクエリを使用します。

mysql> select StudentId from DemoTable where StudentId LIKE 'S%' order by
substring(StudentId,1,9), substring(StudentId,10)*1;

出力結果

+-----------+
| StudentId |
+-----------+
| S/TU/10   |
| S/TU/100  |
| S/TU/1000 |
+-----------+
3 rows in set (0.00 sec)

今度は「10 → 100 → 1000」という、数値として正しい順序で並びました。

クエリの仕組みを解説

このクエリのポイントは以下の3点です。

• LIKE 'S%'：先頭が「S」で始まるレコードのみを対象に絞り込みます。
• substring(StudentId,1,9)：先頭から9文字分（「S/TU/」などの接頭辞＋桁揃え部分）を第一次ソートキーとして使用します。
• substring(StudentId,10)*1：10文字目以降の文字列を取り出し、「*1」を掛けることで暗黙的に数値へ変換。これにより「10」「100」「1000」が数値の大小順に並びます。

このように、文字列の一部を数値として扱うことで、英数字が混在するデータでも自然な順序（ナチュラルソート）での並べ替えが実現できます。桁数が異なるIDやコードを管理している場合にぜひ活用してください。