MySQLで文字列に含まれる数値から最大値を取得する方法

MySQLで文字列から数値部分の最大値を抽出するには

「STU201」のように、文字列と整数が混在したデータから数値部分の最大値を取得したい場合は、CAST()関数とMAX()関数を組み合わせて使用します。文字列のまま比較すると意図しない結果になるため、SUBSTRING()で数値部分を切り出し、CAST()で数値型に変換してからMAX()で最大値を求めるのがポイントです。

以下、具体的な手順をサンプルコードとともに解説します。

1. サンプルテーブルを作成する

まず、テスト用のテーブルを作成します。

mysql> create table DemoTable
(
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    StudentBookCode varchar(200)
);
Query OK, 0 rows affected (0.56 sec)

2. サンプルデータを挿入する

INSERT文を使って、文字列と整数が混在したデータを登録します。

mysql> insert into DemoTable(StudentBookCode) values('STU201');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU202');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU203');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU290');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(StudentBookCode) values('STU234');
Query OK, 1 row affected (0.15 sec)

3. 登録されたデータを確認する

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

mysql> select *from DemoTable;

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

+----+-----------------+
| Id | StudentBookCode |
+----+-----------------+
| 1  | STU201          |
| 2  | STU202          |
| 3  | STU203          |
| 4  | STU290          |
| 5  | STU234          |
+----+-----------------+
5 rows in set (0.00 sec)

4. 数値部分の最大値を取得する

SUBSTRING()関数で先頭の「STU」を除いた数値部分を取り出し、CAST()でUNSIGNED（符号なし整数）に変換したうえで、MAX()関数で最大値を求めます。

mysql> select MAX(CAST(SUBSTRING(StudentBookCode FROM 4) AS UNSIGNED)) from DemoTable;

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

+----------------------------------------------------------+
| MAX(CAST(SUBSTRING(StudentBookCode FROM 4) AS UNSIGNED)) |
+----------------------------------------------------------+
| 290                                                      |
+----------------------------------------------------------+
1 row in set (0.00 sec)

解説：各関数の役割

• SUBSTRING(StudentBookCode FROM 4)：4文字目以降（つまり「STU」より後ろ）の文字列を切り出します。「STU290」なら「290」となります。
• CAST(〜 AS UNSIGNED)：切り出した文字列を符号なし整数に変換します。これにより数値として正しく比較できるようになります。
• MAX()：変換後の数値の中から最大値を返します。この例では「290」が結果として得られます。

この手法は、接頭辞付きのコード（学生番号、商品コードなど）から連番の最大値を取得し、次の番号を採番したい場合などに非常に便利です。文字列同士を直接比較すると「STU9」が「STU234」より大きいと判定されるといった問題が起こるため、必ずCAST()で数値化してから比較するようにしましょう。