MySQLで特定の文字で始まる英数字文字列の列から最大値を取得する方法

概要

MySQLで、特定の文字（例：「IT」）で始まる英数字の文字列が格納された列から最大値を取得したいケースはよくあります。このような場合には、MAX()関数とCAST()関数を組み合わせて使用します。さらに、目的のレコードだけを絞り込むためにRLIKE演算子を活用します。

以下、テーブル作成から順を追って具体的な手順を解説します。

1. テーブルを作成する

まず、サンプル用のテーブルを作成します。

mysql> create table DemoTable1381
    -> (
    -> DepartmentId varchar(40)
    -> );
Query OK, 0 rows affected (0.48 sec)

2. レコードを挿入する

INSERT文を使って、テーブルにいくつかのレコードを追加します。

mysql> insert into DemoTable1381 values('IT794');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable1381 values('AT1034');
Query OK, 1 row affected (0.52 sec)
mysql> insert into DemoTable1381 values('IT967');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1381 values('IT874');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1381 values('AT967');
Query OK, 1 row affected (0.09 sec)

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

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

mysql> select * from DemoTable1381;

実行すると、次のような結果が出力されます。

+--------------+
| DepartmentId |
+--------------+
| IT794        |
| AT1034       |
| IT967        |
| IT874        |
| AT967        |
+--------------+
5 rows in set (0.00 sec)

4. 特定の文字で始まる値の最大値を取得する

ここでは、「IT」で始まる英数字文字列の中から最大値を取得します。そのためのクエリは次のとおりです。

mysql> select max(cast(substr(trim(DepartmentId),3) AS UNSIGNED)) from DemoTable1381 where DepartmentId RLIKE 'IT';

実行結果は以下のようになります。

+-----------------------------------------------------+
| max(cast(substr(trim(DepartmentId),3) AS UNSIGNED)) |
+-----------------------------------------------------+
|                                                 967 |
+-----------------------------------------------------+
1 row in set (0.10 sec)

クエリのポイント解説

このクエリでは、以下の処理が行われています。

• WHERE DepartmentId RLIKE 'IT' … DepartmentId列に「IT」を含むレコードだけを対象にします。
• trim(DepartmentId) … 余分な空白を取り除きます。
• substr(…, 3) … 3文字目以降、つまり接頭辞「IT」を除いた数値部分を抽出します。
• CAST(… AS UNSIGNED) … 抽出した文字列を符号なし整数に変換し、数値として正しく比較できるようにします。
• MAX() … 変換後の数値の中から最大値を返します。

なお、RLIKE 'IT'は「ITを含む」という意味になるため、厳密に先頭一致させたい場合はRLIKE '^IT'のように正規表現のアンカー「^」を使うとより安全です。

このように、MAX()・CAST()・SUBSTR()・RLIKEを組み合わせれば、特定の接頭辞を持つ英数字文字列から数値部分の最大値を簡単に取得できます。部署コードや商品番号など、接頭辞付きのIDを扱う場面でぜひ活用してください。