MySQLで文字列と整数が混在するカラムから最大値を取得する方法

MySQLで文字列と整数が混在するカラムから最大値を取得する方法

「INV129」や「INV145」のように、接頭辞（プレフィックス）付きの文字列と整数が混在するカラムから最大値を取得したいケースはよくあります。しかし、単純にMAX()関数を使うだけでは正しい結果が得られません。文字列として比較されるため、「INV9」より「INV10」の方が小さいと判断されてしまうからです。

この問題を解決するには、SUBSTRING()関数で数値部分だけを取り出し、CAST()関数で符号なし整数型（UNSIGNED）に変換した上でMAX()関数を適用します。

基本構文

SELECT MAX(CAST(SUBSTRING(yourColumnName, 4, length(yourColumnName)-3) AS UNSIGNED)) AS anyVariableName FROM yourTableName;

この構文では、SUBSTRING()の第2引数「4」によって先頭の3文字（接頭辞）を除いた4文字目以降を抽出し、第3引数の「length(カラム名)-3」で残りの文字数を指定しています。これにより数値部分のみが取り出され、CAST()によって整数として正しく比較できるようになります。

サンプルテーブルの作成

それでは、実際に動作を確認してみましょう。まずはテスト用のテーブルを作成します。

mysql> create table StringIntMixHighestDemo
    -> (
    -> InvoiceId int NOT NULL AUTO_INCREMENT,
    -> InvoiceNumber varchar(20),
    -> PRIMARY KEY(InvoiceId)
    -> );
Query OK, 0 rows affected (0.65 sec)

テストデータの挿入

次に、INSERT文を使って請求書番号のサンプルデータを挿入します。

mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV129');
Query OK, 1 row affected (0.11 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV122');
Query OK, 1 row affected (0.22 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV1');
Query OK, 1 row affected (0.15 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV145');
Query OK, 1 row affected (0.18 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV19');
Query OK, 1 row affected (0.10 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV134');
Query OK, 1 row affected (0.13 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV135');
Query OK, 1 row affected (0.16 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV10');
Query OK, 1 row affected (0.14 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV100');
Query OK, 1 row affected (0.11 sec)
mysql> insert into StringIntMixHighestDemo(InvoiceNumber) values('INV121');
Query OK, 1 row affected (0.27 sec)

登録データの確認

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

mysql> select *from StringIntMixHighestDemo;

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

+-----------+---------------+
| InvoiceId | InvoiceNumber |
+-----------+---------------+
|         1 | INV129        |
|         2 | INV122        |
|         3 | INV1          |
|         4 | INV145        |
|         5 | INV19         |
|         6 | INV134        |
|         7 | INV135        |
|         8 | INV10         |
|         9 | INV100        |
|        10 | INV121        |
+-----------+---------------+
10 rows in set (0.00 sec)

最大値を取得するクエリ

ここで本題の、文字列と整数が混在するカラムから最大値を取得するクエリを実行します。

mysql> SELECT MAX(CAST(SUBSTRING(InvoiceNumber, 4, length(InvoiceNumber)-3) AS UNSIGNED)) as HighestValue
    -> from StringIntMixHighestDemo;

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

+--------------+
| HighestValue |
+--------------+
|          145 |
+--------------+
1 row in set (0.00 sec)

まとめ

上記の結果から、数値部分が最も大きい「INV145」の「145」が正しく最大値として返されていることがわかります。もしSUBSTRING()とCAST()を使わずにそのままMAX(InvoiceNumber)を実行すると、文字列比較では「INV9」が「INV10」より大きいと判定されるなど、意図しない結果になってしまうため注意が必要です。

なお、SUBSTRING()の開始位置やオフセットは、接頭辞の文字数に応じて適宜調整してください。例えば接頭辞が「ABC-」のような4文字の場合は、開始位置を5、オフセットを「length(カラム名)-4」に変更します。