MySQLで「456 John Smith」のような数字入りの文字列をアルファベット順にORDER BYで並べ替える方法

MySQLで数字入りの文字列を文字（アルファベット）順に並べ替えるには

「456 John Smith」のように、先頭に数字が付いた文字列データが格納されたカラムを、数字ではなく後ろの文字（アルファベット）部分で並べ替えたいケースがあります。このような場合、ORDER BY句とSUBSTRING()関数を組み合わせることで実現できます。

具体的には、LOCATE()関数で最初のスペースの位置を特定し、SUBSTRING()関数でその後ろの文字列を抽出することで、名前部分だけを基準にソートを行います。

サンプルテーブルの作成

まず、以下のクエリでテーブルを作成しましょう。

mysql> create table DemoTable
(
    Id varchar(100)
);
Query OK, 0 rows affected (0.65 sec)

レコードの挿入

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

mysql> insert into DemoTable values('456 John Smith');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('897 Adam Smith');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('1009 Bob Smith');
Query OK, 1 row affected (0.13 sec)

登録済みレコードの確認

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

mysql> select *from DemoTable;

このクエリを実行すると、次のような結果が得られます。

+----------------+
| Id             |
+----------------+
| 456 John Smith |
| 897 Adam Smith |
| 1009 Bob Smith |
+----------------+
3 rows in set (0.00 sec)

アルファベット部分で並べ替えるクエリ

以下が、文字（アルファベット）部分を基準にORDER BYでソートするためのクエリです。

mysql> select *from DemoTable order by SUBSTRING(Id,LOCATE(' ', Id));

このクエリを実行すると、次のような出力になります。

+----------------+
| Id             |
+----------------+
| 897 Adam Smith |
| 1009 Bob Smith |
| 456 John Smith |
+----------------+
3 rows in set (0.05 sec)

クエリの仕組み

まず、LOCATE(' ', Id)によって各文字列内にある最初のスペースの位置を取得します。次に、SUBSTRING(Id, LOCATE(' ', Id))により、そのスペース以降の文字列（例：「John Smith」「Adam Smith」「Bob Smith」）が抽出されます。

その結果、先頭の数字部分は無視され、名前のアルファベット順（Adam → Bob → John）に正しく並べ替えられていることが確認できます。IDの桁数が異なる場合でも、常に名前部分だけで安定したソートが行えるのがこの方法の利点です。