MySQLで文字列や数値が混在するカラムから特定のレコードを取得する方法

文字列・数値・特殊文字が混在するカラムから特定のレコードを取得したい場合は、ORDER BY CAST() を活用すると便利です。この記事では、SUBSTRING() 関数と組み合わせて、「指定した文字列で始まるレコードの中から、数値部分が最も大きいもの」を抽出する方法を、実際のSQL例とともにわかりやすく解説します。

サンプルテーブルの作成

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

mysql> create table DemoTable2006
(
    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    UserCode varchar(20)
);
Query OK, 0 rows affected (1.14 sec)

レコードの挿入

続いて、INSERT文を使ってテーブルにデータを追加していきます。

mysql> insert into DemoTable2006(UserCode) values('John_12');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable2006(UserCode) values('John_34');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable2006(UserCode) values('John_56');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_101');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_103');
Query OK, 1 row affected (0.37 sec)
mysql> insert into DemoTable2006(UserCode) values('Chris_106');
Query OK, 1 row affected (0.07 sec)

登録済みレコードの確認

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

mysql> select * from DemoTable2006;

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

+--------+-----------+
| UserId | UserCode  |
+--------+-----------+
|      1 | John_12   |
|      2 | John_34   |
|      3 | John_56   |
|      4 | Chris_101 |
|      5 | Chris_103 |
|      6 | Chris_106 |
+--------+-----------+
6 rows in set (0.00 sec)

特定のレコードを取得するクエリ

ここでは、「John」で始まるUserCodeの中から、末尾の数値が最も大きいレコードを1件だけ取得します。以下のクエリを使用します。

mysql> select * from DemoTable2006
    -> where UserCode like 'John%'
    -> order by cast(substring(UserCode from 7) as signed) desc
    -> limit 1;

実行結果：

+--------+----------+
| UserId | UserCode |
+--------+----------+
|      3 | John_56  |
+--------+----------+
1 row in set (0.00 sec)

クエリのポイント解説

このクエリが期待どおりに動作する仕組みを、各要素ごとに見ていきましょう。

• WHERE UserCode like 'John%'：前方一致検索により、「John」で始まるレコードだけを対象に絞り込みます。
• SUBSTRING(UserCode from 7)：7文字目以降の文字列（この例では「12」「34」「56」という数値部分）を取り出します。
• CAST(… AS SIGNED)：取り出した文字列を符号付き整数型に変換することで、文字列比較ではなく正しい数値比較が可能になります。
• ORDER BY … DESC：変換後の数値を降順に並べ替えます。
• LIMIT 1：並べ替えた結果の先頭1件、すなわち数値が最大のレコードのみを返します。

このように SUBSTRING() と CAST() を組み合わせることで、文字列と数値が混在するカラムでも柔軟に並べ替えや絞り込みが行えます。接頭辞（プレフィックス）の長さが変わる場合は、SUBSTRINGの開始位置を実際のデータ形式に合わせて調整してください。