MySQLで特定の数値を含む値を検索・表示する方法｜SUBSTRING_INDEX()を使った並べ替えテクニック

MySQLで特定の数値を含む値を検索して表示したい場合、SUBSTRING_INDEX()関数が便利です。この関数を活用することで、指定した数値が文字列内に出現する位置に基づいて結果を並べ替えることも可能になります。

まずはサンプル用のテーブルを作成しましょう。

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

例：データの挿入

INSERTコマンドを使用して、テーブルにいくつかのレコードを挿入します。

mysql> insert into DemoTable values('235678');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable values('1634990');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable values('678590');
Query OK, 1 row affected (0.46 sec)
mysql> insert into DemoTable values('908765432');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('222343388773');
Query OK, 1 row affected (0.35 sec)
mysql> insert into DemoTable values('09028215');
Query OK, 1 row affected (0.51 sec)

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

mysql> select *from DemoTable;

出力結果

+--------------+
| Number       |
+--------------+
| 235678       |
| 1634990      |
| 678590       |
| 908765432    |
| 222343388773 |
| 09028215     |
+--------------+
6 rows in set (0.00 sec)

特定の数値を含む値を取得するクエリ

以下のクエリでは、LIKE句で「5」を含むレコードを抽出し、さらにSUBSTRING_INDEX()を使って「5」より前の部分の文字数順に並べ替えています。これにより、目的の数値が先頭に近いレコードほど上位に表示されます。

mysql> select Number from DemoTable where Number LIKE '%5%' ORDER BY LENGTH(SUBSTRING_INDEX(Number, '5', 1));

出力結果

+-----------+
| Number    |
+-----------+
| 235678    |
| 678590    |
| 908765432 |
| 09028215  |
+-----------+
4 rows in set (0.00 sec)

ポイント解説

SUBSTRING_INDEX(Number, '5', 1)は、カラムの値を区切り文字「5」で分割した際の最初の部分を返します。その長さをLENGTH()で取得し、ORDER BYに指定することで、「5」が出現する位置が早い順にソートされる仕組みです。

この手法は、インクリメンタルサーチのように「前方一致の候補を優先的に表示したい」ケースで特に有効です。LIKE句による絞り込みと組み合わせることで、ユーザーにとって直感的な検索結果の順序を実現できます。