MySQLで数字を含む文字列を自然な順序で並べ替える方法

MySQLで数字を含む文字列を自然な順序で並べ替える方法

MySQLで「2J John has 9」や「2J John has 58」のように数字が混ざった文字列を並べ替えたい場合、単純なORDER BYでは文字列単位で比較されるため、意図したとおりの順序にならないことがあります。このようなときは、REGEXP_REPLACE関数やLENGTH関数をORDER BY句と組み合わせることで、人間にとって自然な順序（自然順ソート）を実現できます。ここでは、実際にテーブルを作成して手順を確認していきましょう。

1. サンプルテーブルの作成

まず、CREATE TABLE文でテーブルを作成します。

mysql> create table demo18
-> (
-> value text
-> );
Query OK, 0 rows affected (1.18 sec)

2. レコードの挿入

続いて、INSERTコマンドでテーブルにデータを登録します。

mysql> insert into demo18 values('John Smith');
Query OK, 1 row affected (0.06 sec)

mysql> insert into demo18 values('2J John has 58');
Query OK, 1 row affected (0.17 sec)

mysql> insert into demo18 values('2J John has 9');
Query OK, 1 row affected (0.09 sec)

3. 登録データの確認

SELECT文でテーブルの内容を表示します。

mysql> select * from demo18;

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

+----------------+
| value          |
+----------------+
| John Smith     |
| 2J John has 58 |
| 2J John has 9  |
+----------------+
3 rows in set (0.00 sec)

4. 自然順ソートを行うクエリ

末尾の数字を考慮して並べ替えるには、以下のようにREGEXP_REPLACEとLENGTHを組み合わせます。

mysql> select * from demo18
-> order by regexp_replace(value, '[0-9]*$', ''),
-> length(value),
-> value;

実行結果は以下のとおりです。「2J John has 9」が「2J John has 58」よりも前に来ている点に注目してください。

+----------------+
| value          |
+----------------+
| 2J John has 9  |
| 2J John has 58 |
| John Smith     |
+----------------+
3 rows in set (0.14 sec)

クエリの仕組み

このクエリでは、ORDER BY句に3つのソートキーを指定しています。それぞれの役割は次のとおりです。

• regexp_replace(value, '[0-9]*$', '')：正規表現を使って文字列末尾の連続する数字を取り除いた値で並べ替えます。これにより、数字以外の部分が共通するレコード同士が同じグループになります。
• length(value)：文字列の長さが短い順に並べます。末尾の数字が小さいほど文字列全体も短くなるため、結果的に数字が小さい順にソートされます。
• value：最後に文字列そのもので比較し、長さが同じ場合の順序を確定させます。

まとめ

数字を含む文字列を自然な順序で並べ替えたい場合は、「末尾の数字を除去した文字列」「文字列の長さ」「元の文字列」の3段階でORDER BYを指定するのがポイントです。ファイル名や型番・商品コードなど、数字が混在するデータを扱う際にぜひ活用してみてください。