MySQL 5.7で文字列末尾から特定の文字列が出現するまでの文字列を抽出する

TL;DR

とある案件で、URLを含む本文から本文末尾に出現するURLのみ抽出して本文とURLを分割してほしいと依頼が来たので、文字列末尾から特定の文字列が出現するまでの文字列を抽出するクエリを作成しました。

SELECT RIGHT(body, INSTR(REVERSE(body), REVERSE('http://')) + CHAR_LENGTH('http://') - 1) AS url FROM articles;

環境

• MySQL 5.7

使用する関数

名前	説明
INSTR	部分文字列が最初に出現する位置のインデックスを返します
REVERSE	文字列内の文字を逆順に並べ替えます
RIGHT	右端から指定された数の文字を返します

引用元: MySQL 5.6 リファレンスマニュアル / 関数と演算子 / 文字列関数 ¹

クエリ解説

サンプルのテーブル

mysql> SELECT * FROM articles;
+----+---------------------------------------------------+
| id | body                                              |
+----+---------------------------------------------------+
|  1 | 本文http://example.com 本文http://example.com      |
+----+---------------------------------------------------+
1 row in set (0.00 sec)

検索したい文字列が1文字の場合

1. REVERSE関数で本文の文字列を逆順に並べ替え、INSTR関数で検索文字列が最初に出現する位置のインデックスを取得
2. 1.で取得したインデックス＝本文の文字列末尾から検索文字列が出現するまでの文字列の長さになるので、そのままRIGHT関数に渡す

mysql> SELECT INSTR(REVERSE(body), 'h') AS `index` FROM articles;
+-------+
| index |
+-------+
|    18 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT(body, INSTR(REVERSE(body), 'h')) AS url FROM articles;
+--------------------+
| url                |
+--------------------+
| http://example.com |
+--------------------+
1 row in set (0.01 sec)

検索したい文字列が2文字以上の場合

1. REVERSE関数で本文の文字列と検索したい文字列を逆順に並べ替え、INSTR関数で検索文字列が最初に出現する位置のインデックスを取得
2. 1.で取得したインデックス＝本文の文字列末尾から検索文字列の末尾が出現するまでの文字列の長さになるので、検索文字列の長さを足して1引いてからRIGHT関数に渡す

mysql> SELECT INSTR(REVERSE(body), REVERSE('http://')) AS `index` FROM articles;
+-------+
| index |
+-------+
|    12 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT(body, INSTR(REVERSE(body), REVERSE('http://')) + CHAR_LENGTH('http://') - 1) AS url FROM articles;
+--------------------+
| url                |
+--------------------+
| http://example.com |
+--------------------+
1 row in set (0.01 sec)

まとめ

実際に使ったときはCASE式とサブクエリを使って、本文とURLを分割して別々のカラムに保存しました。
開発や運用の都合で、ときどきこの手の泥臭いクエリが必要になることがあるので、文字列操作の関数は一通り目を通しておこうと思いました。

追記(2020/06/03)

とある先輩エンジニアに今回のケースならSUBSTRING_INDEX関数で取得できるとアドバイスを貰ったので追記します。

SELECT CONCAT('http://', SUBSTRING_INDEX(body, 'http://', -1)) AS url FROM articles;

SUBSTRING_INDEX関数は区切り文字が指定された回数出現する前の部分文字列を返しますが、第3引数に負の数を指定すると文字列の末尾から検索します。

mysql> SELECT SUBSTRING_INDEX(body, 'http://', -1) FROM articles;
+--------------------------------------+
| SUBSTRING_INDEX(body, 'http://', -1) |
+--------------------------------------+
| example.com                          |
+--------------------------------------+
1 row in set (0.00 sec)

あとはCONCAT関数で区切り文字列と結合するだけですね。

mysql> SELECT CONCAT('http://', SUBSTRING_INDEX(body, 'http://', -1)) AS url FROM articles;
+--------------------+
| url                |
+--------------------+
| http://example.com |
+--------------------+
1 row in set (0.01 sec)

1. MySQL 5.7のリファレンスは日本語化されていなかったので5.6にしておきました。 ↩