MySQLで数字を含む文字列を正しく並べ替える方法（ORDER BY・SUBSTR・CAST活用）

はじめに

MySQLでは、文字列型のカラムに「A10」「A30」のような数字が混ざったデータが格納されている場合、通常の ORDER BY だけでは文字コード順にソートされるため、「A10、A11、A12…」という数値として自然な順序になりません。

この問題を解決するには、ORDER BY 句と SUBSTR() 関数、さらに CAST() 関数を組み合わせます。以下がその基本構文です。

SELECT * FROM yourTableName ORDER BY
SUBSTR(yourColumnName FROM 1 FOR 2),
CAST(SUBSTR(yourColumnName FROM 2) AS UNSIGNED);

構文のポイント

• SUBSTR(カラム名 FROM 1 FOR 2)：先頭から2文字目まで（接頭辞部分）を取り出し、文字列として比較します。
• CAST(SUBSTR(カラム名 FROM 2) AS UNSIGNED)：2文字目以降の数字部分を符号なし整数に変換し、数値として比較します。

これにより、接頭辞ごとにグループ化され、その中で数字部分が数値の大小順に並びます。

動作確認用テーブルの作成

実際に動きを確認するため、サンプルテーブルを作成しましょう。テーブル作成のクエリは以下の通りです。

mysql> create table OrderByStringWithNumbers
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> Words varchar(10),
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.86 sec)

レコードの挿入

次に、INSERT コマンドを使っていくつかのレコードを挿入します。

mysql> insert into OrderByStringWithNumbers(Words) values('A10');
Query OK, 1 row affected (0.19 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A30');
Query OK, 1 row affected (0.19 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A12');
Query OK, 1 row affected (0.13 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A11');
Query OK, 1 row affected (0.17 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A28');
Query OK, 1 row affected (0.13 sec)

mysql> insert into OrderByStringWithNumbers(Words) values('A21');
Query OK, 1 row affected (0.20 sec)

登録データの確認

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

mysql> select *from OrderByStringWithNumbers;

実行結果は以下の通りです。挿入した順序のまま格納されていることがわかります。

+----+-------+
| Id | Words |
+----+-------+
| 1  | A10   |
| 2  | A30   |
| 3  | A12   |
| 4  | A11   |
| 5  | A28   |
| 6  | A21   |
+----+-------+
6 rows in set (0.00 sec)

数字を含む文字列での並べ替えクエリ

それでは、SUBSTR() と CAST() を使って、数字部分を数値として扱うソートを実行してみます。

mysql> select *from OrderByStringWithNumbers order by
    -> substr(Words from 1 for 2),
    -> cast(substr(Words from 2) AS UNSIGNED);

実行結果

+----+-------+
| Id | Words |
+----+-------+
| 1  | A10   |
| 4  | A11   |
| 3  | A12   |
| 6  | A21   |
| 5  | A28   |
| 2  | A30   |
+----+-------+
6 rows in set (0.00 sec)

結果を見ると、「A10 → A11 → A12 → A21 → A28 → A30」のように、数字部分が数値として正しい昇順で並んでいることが確認できます。もし単純に ORDER BY Words とした場合も、この例では同じ結果になりますが、桁数が異なる「A9」と「A10」などが混在すると文字列比較では「A10 < A9」となるため、CAST() を使った方法が確実です。

まとめ

• 数字入りの文字列カラムをソートするには、SUBSTR() で接頭辞と数字部分を分離する。
• 数字部分は CAST(... AS UNSIGNED) で整数に変換してから ORDER BY に渡す。
• これにより、人間にとって自然な「数値順」の並べ替えが実現できる。