MySQLで長さの異なる英数字データを正しく並べ替える方法

MySQLでは、STU-MIT-143 のような英数字の文字列を単純に ORDER BY で並べ替えると、辞書順（文字コード順）になってしまい、意図した数値順にならないことがあります。本記事では、SUBSTRING_INDEX() 関数を活用して、長さの異なる英数字データを正しくソートする方法を解説します。

サンプルテーブルの作成

まず、以下のコマンドでテーブルを作成します。

mysql> create table DemoTable1623
    -> (
    -> StudentCode varchar(20)
    -> );
Query OK, 0 rows affected (0.47 sec)

レコードの挿入

INSERT文を使って、テーブルにいくつかのレコードを挿入します。

mysql> insert into DemoTable1623 values('STU-MIT-143');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1623 values('STU-MIT-10');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1623 values('STU-MIT-150');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable1623 values('STU-MIT-148');
Query OK, 1 row affected (0.22 sec)

登録データの確認

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

mysql> select * from DemoTable1623;

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

+-------------+
| StudentCode |
+-------------+
| STU-MIT-143 |
| STU-MIT-10  |
| STU-MIT-150 |
| STU-MIT-148 |
+-------------+
4 rows in set (0.00 sec)

このまま ORDER BY StudentCode とすると、文字列として比較されるため、STU-MIT-10 が先頭に来ず、期待どおりの順序になりません。

英数字カラムを正しく並べ替えるクエリ

そこで、区切り文字を基準に分割して並べ替えるのが有効です。以下がそのクエリです。

mysql> select * from DemoTable1623
    -> order by substring_index(StudentCode, '-', 1),
    -> substring_index(StudentCode, '-', -1) + 0;

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

+-------------+
| StudentCode |
+-------------+
| STU-MIT-10  |
| STU-MIT-143 |
| STU-MIT-148 |
| STU-MIT-150 |
+-------------+
4 rows in set (0.00 sec)

ポイント解説

このクエリのポイントは次の2点です。

• substring_index(StudentCode, '-', 1)：区切り文字「-」より前の部分（プレフィックス）で並べ替えます。
• substring_index(StudentCode, '-', -1) + 0：「-」より後ろの数値部分を取り出し、「+ 0」によって数値として評価することで、10 や 143 のように桁数が異なる数字でも正しい数値順に並びます。

このように SUBSTRING_INDEX() 関数と数値変換を組み合わせることで、長さの異なる英数字データでも自然な順序でソートできます。学生コードや商品コードなど、プレフィックス＋連番形式のデータを扱う際にぜひ活用してください。