MySQLで区切り文字間のテキストを抽出する方法｜LOCATE()とSUBSTR()の使い方

はじめに

MySQLで区切り文字（デリミタ）の間にあるテキストを抽出したい場合は、LOCATE()関数とSUBSTR()関数を組み合わせることで実現できます。本記事では、区切り文字としてコロン（:）を使用した例を紹介しますが、カンマやハイフンなど、任意の文字に変更して利用することも可能です。

基本構文

以下の構文は、区切り文字の直後にある単語を検索・抽出するためのものです。

SELECT
   SUBSTR(yourColumnName,
      LOCATE(':',yourColumnName)+1,
         (CHAR_LENGTH(yourColumnName) - LOCATE(':',REVERSE(yourColumnName)) - LOCATE(':',yourColumnName))) AS anyAliasName
FROM yourTableName;

構文の仕組み

• LOCATE(':', yourColumnName)：最初の区切り文字の位置を取得します
• SUBSTR(...)：その位置の次の文字から抽出を開始します
• CHAR_LENGTH() と REVERSE() の組み合わせ：最後の区切り文字までの長さを計算し、抽出する文字数を求めます

つまり、このクエリは「最初の区切り文字」と「最後の区切り文字」の間にあるテキストを取り出す処理を行っています。

サンプルテーブルの作成

実際に動作を確認するため、まずテーブルを作成しましょう。テーブル作成用のクエリは以下の通りです。

mysql> create table SearchTextBetweenDelimitersDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Words longtext,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.67 sec)

レコードの挿入

続いて、INSERTコマンドを使ってサンプルデータを挿入します。

mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('Object:Oriented:Programming');
Query OK, 1 row affected (0.21 sec)
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('C++:Java:C#');
Query OK, 1 row affected (0.21 sec)
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('SQLServer:MySQL:Oracle');
Query OK, 1 row affected (0.17 sec)
mysql> insert into SearchTextBetweenDelimitersDemo(Words) values('SQL SQLServer:MySQL:Oracle');
Query OK, 1 row affected (0.18 sec)

登録データの確認

SELECT文でテーブル内の全レコードを表示してみましょう。

mysql> select *from SearchTextBetweenDelimitersDemo;

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

+----+-----------------------------+
| Id | Words                       |
+----+-----------------------------+
|  1 | Object:Oriented:Programming |
|  2 | C++:Java:C#                 |
|  3 | SQLServer:MySQL:Oracle      |
|  4 | SQL SQLServer:MySQL:Oracle  |
+----+-----------------------------+
4 rows in set (0.00 sec)

区切り文字間のテキストを抽出するクエリ

それでは、区切り文字（コロン）の間にある単語を検索して表示するクエリを実行します。

mysql> SELECT
    -> SUBSTR(Words,
    -> LOCATE(':',Words)+1,
    -> (CHAR_LENGTH(Words) - LOCATE(':',REVERSE(Words)) - LOCATE(':',Words))) AS Text
   -> FROM SearchTextBetweenDelimitersDemo;

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

+----------+
| Text     |
+----------+
| Oriented |
| Java     |
| MySQL    |
| MySQL    |
+----------+
4 rows in set (0.00 sec)

まとめ

上記の出力結果を見ると、各レコードから「最初のコロン」と「最後のコロン」の間にあるテキストが正しく抽出されていることがわかります。たとえば「Object:Oriented:Programming」からは「Oriented」、「SQLServer:MySQL:Oracle」からは「MySQL」が取得できています。

この手法は、ログデータやタグ情報など、区切り文字で連結された文字列から特定の部分だけを取り出したい場合に非常に便利です。区切り文字を変更すれば、さまざまなフォーマットのデータに柔軟に対応できますので、ぜひ活用してみてください。