MySQLで英字と数字が混在する列の値を正しくソートする方法

MySQLでは、英字と数字が混在するデータ（例：「J23」「C13」など）が格納された列を単純にORDER BY句でソートすると、すべて文字列として扱われるため、意図した通りの順序にならないことがあります。本記事では、LEFT関数・SUBSTR関数・CAST関数を組み合わせて、文字部分と数値部分をそれぞれ正しく並べ替える方法を、実際のSQL例とともに解説します。

サンプルテーブルの作成

まず、ソート対象となるテーブルを作成しましょう。

mysql> create table DemoTable
    -> (
    -> Code varchar(20)
    -> );
Query OK, 0 rows affected (0.70 sec)

レコードの挿入

INSERTコマンドを使用して、英字と数字が混在するレコードをいくつか挿入します。

mysql> insert into DemoTable values('J23');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('C13');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable values('A61');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values('D56');
Query OK, 1 row affected (0.11 sec)

登録済みレコードの確認

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

mysql> select * from DemoTable;

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

+------+
| Code |
+------+
| J23  |
| C13  |
| A61  |
| D56  |
+------+
4 rows in set (0.00 sec)

英字と数字が混在する値をソートするクエリ

それでは、文字値を正しくソートするためのクエリを見ていきましょう。

mysql> select * from DemoTable
    -> order by left(Code,1),
    -> cast(substr(Code, 2) as unsigned);

実行結果：

+------+
| Code |
+------+
| A61  |
| C13  |
| D56  |
| J23  |
+------+
4 rows in set (0.05 sec)

クエリのポイント解説

このクエリが正しく動作する仕組みは、次の2つの条件に分けられます。

① left(Code,1)
LEFT関数で文字列の先頭1文字（この例では英字部分）を取り出し、まずアルファベット順にソートします。これにより、A→C→D→Jの順に並びます。

② cast(substr(Code, 2) as unsigned)
SUBSTR関数で2文字目以降の数値部分を取り出し、CAST関数によって符号なし整数（UNSIGNED）に変換しています。数値が文字列ではなく数値として比較されるため、たとえば「10」と「9」のような場合でも、「10」が「9」の後ろに正しく配置されます。

このように、ORDER BY句に複数の条件を組み合わせることで、英字と数字が混在する列でも自然な順序でソートすることが可能になります。製品コードや社員番号など、プレフィックス＋連番形式のデータを扱う際にぜひ活用してください。