MySQLのSUBSTRING_INDEX()関数でURLの一部を抽出する方法

MySQLでURLの一部を抽出したい場合は、SUBSTRING_INDEX()関数を使用します。この関数を使うと、指定した区切り文字を基準に、文字列から目的の部分だけを簡単に取り出せます。

SUBSTRING_INDEX()関数の基本構文

SUBSTRING_INDEX(対象文字列, 区切り文字, 出現回数)

• 第1引数：処理対象となる文字列
• 第2引数：区切り文字として扱う文字
• 第3引数：正の値を指定すると左端から数え、負の値を指定すると右端から数えた位置を基準に部分文字列を返します

それでは、実際の例を通じて使い方を確認していきましょう。

サンプルテーブルの作成

まず、URLを格納するためのテーブルを作成します。

mysql> create table DemoTable
(
    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    URL text
);
Query OK, 0 rows affected (0.53 sec)

サンプルデータの挿入

続いて、INSERTコマンドでレコードを挿入します。

mysql> insert into DemoTable(URL) values('https:\\www.example.com\\homepage');
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable(URL) values('https:\\www.onlinetest.com\\welcome\\indexpage');
Query OK, 1 row affected (0.12 sec)

登録データの確認

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

mysql> select *from DemoTable;

実行結果は以下のとおりです。ここではバックスラッシュ（＼）が1つしか表示されていませんが、これはMySQLが内部的にエスケープ用のスラッシュを1つ除去して格納・表示しているためです。

+----+---------------------------------------------+
| Id | URL                                         |
+----+---------------------------------------------+
| 1  | https:\www.example.com\homepage             |
| 2  | https:\www.onlinetest.com\welcome\indexpage |
+----+---------------------------------------------+
2 rows in set (0.00 sec)

SUBSTRING_INDEX()でURLの末尾部分を抽出する

それでは、URLから最後のセグメント（パスの末尾）を抽出するクエリを実行します。

mysql> select substring_index(URL,'\\',-1) from DemoTable;

第3引数に-1を指定しているため、右端から1番目の区切り文字より後ろの文字列、つまりURLの末尾部分が取得されます。

+------------------------------+
| substring_index(URL,'\\',-1) |
+------------------------------+
| homepage                     |
| indexpage                    |
+------------------------------+
2 rows in set (0.00 sec)

補足：スラッシュ区切りの一般的なURLの場合

実際のWebサイトのURLは、一般的にスラッシュ（/）で区切られています。その場合も考え方は同じで、区切り文字を「/」に変更するだけで対応できます。

mysql> select substring_index('https://www.example.com/homepage', '/', -1);
+--------------------------------------------------------------+
| substring_index('https://www.example.com/homepage', '/', -1) |
+--------------------------------------------------------------+
| homepage                                                     |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

また、第3引数を1や2などの正の値にすれば、ドメイン名など先頭側の要素を取り出すことも可能です。SUBSTRING_INDEX()関数を活用すれば、URLやファイルパスなど、区切り文字を含む文字列の加工が柔軟に行えます。