MySQLで文字列と数値が混在するvarcharデータを昇順にソートする方法

MySQLで文字列と数値が混在するvarcharデータを昇順にソートする方法

MySQLでは、ORDER BY句とIF()関数、CAST()関数を組み合わせることで、文字列と数値が混在するvarchar型カラムのデータを昇順にソートできます。ここでは、数値を先に数値順で並べ、文字列を後に配置する方法を、実際のコード例とともに解説します。

サンプルテーブルの作成

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

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

レコードの挿入

続いて、INSERTコマンドを使って数値と文字列を含むレコードを挿入します。

mysql> insert into DemoTable values('190');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('100');
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('120');
Query OK, 1 row affected (0.21 sec)

テーブルの内容を確認する

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

mysql> select *from DemoTable;

以下のような出力が得られます。

+--------------+
| EmployeeCode |
+--------------+
| 190          |
| 100          |
| John         |
| 120          |
+--------------+
4 rows in set (0.00 sec)

文字列と数値を昇順にソートするクエリ

両者が混在するvarcharデータを昇順でソートするには、次のクエリを実行します。

mysql> select *from DemoTable
ORDER BY IF(CAST(EmployeeCode AS SIGNED) = 0, 100000000000, CAST(EmployeeCode AS SIGNED));

実行結果

出力は以下の通りです。数値が先に正しくソートされ、文字列は最後に配置されています。

+--------------+
| EmployeeCode |
+--------------+
| 100          |
| 120          |
| 190          |
| John         |
+--------------+
4 rows in set, 1 warning (0.00 sec)

クエリの仕組み

このソートがどのように機能するのか、ポイントを整理します。

• CAST(EmployeeCode AS SIGNED)：各値を符号付き整数に変換します。数値に変換できない文字列（例：'John'）は0として扱われます。
• IF()関数：キャスト結果が0の場合（＝数値として無効な場合）、非常に大きな値「100000000000」を代わりのソートキーとして使用します。
• その結果、有効な数値は本来の値のまま昇順ソートされ、文字列は大きな値に置き換わるため自動的に末尾へ配置されます。

なお、実行結果に「1 warning」と表示されるのは、文字列'John'を数値へキャストした際の変換警告であり、ソート結果自体には影響ありません。