メインコンテンツまでスキップ
に最終更新

SUBSTRING_INDEX

説明​

SUBSTRING_INDEX 関数は、指定した区切り文字を基準に文字列の一部を抽出します。区切り文字の出現回数を指定することで、左側または右側から抽出できます。

構文​

SUBSTRING_INDEX(<content>, <delimiter>, <field>)

パラメータ​

パラメータ説明
<content>抽出元の文字列。型: VARCHAR
<delimiter>区切り文字列。大文字と小文字を区別し、マルチバイト文字に対応します。型: VARCHAR
<field>区切り文字の出現回数。正の値は左から、負の値は右から数えます。0 の場合は空文字列を返します。型: INT

戻り値​

抽出した部分文字列を VARCHAR 型で返します。

抽出規則:

  • field > 0 の場合: 左から数えて field 番目の区切り文字より前の部分を返します。
  • field < 0 の場合: 右から数えて |field| 番目の区切り文字より後の部分を返します。
  • field = 0 の場合: 空文字列を返します（content が NULL の場合は NULL を返します）。
  • 区切り文字は大文字と小文字を区別し、完全一致で照合します。

特殊なケース:

  • いずれかのパラメータが NULL の場合は NULL を返します。
  • 区切り文字が文字列に存在しない場合は、元の文字列を返します。
  • 指定した回数が区切り文字の実際の出現回数を超える場合は、抽出可能な最大範囲を返します。
  • 区切り文字が空文字列の場合は、空文字列を返します。
  • 抽出元の文字列が空の場合は、空文字列を返します。

例​

  1. 左側からの基本的な抽出
SELECT SUBSTRING_INDEX('hello world', ' ', 1), SUBSTRING_INDEX('one,two,three', ',', 2);
+----------------------------------------+------------------------------------------+
| SUBSTRING_INDEX('hello world', ' ', 1) | SUBSTRING_INDEX('one,two,three', ',', 2) |
+----------------------------------------+------------------------------------------+
| hello                                  | one,two                                  |
+----------------------------------------+------------------------------------------+
  1. 右側からの抽出（負の値を指定）
SELECT SUBSTRING_INDEX('hello world', ' ', -1), SUBSTRING_INDEX('one,two,three', ',', -1);
+-----------------------------------------+-------------------------------------------+
| SUBSTRING_INDEX('hello world', ' ', -1) | SUBSTRING_INDEX('one,two,three', ',', -1) |
+-----------------------------------------+-------------------------------------------+
| world                                   | three                                     |
+-----------------------------------------+-------------------------------------------+
  1. NULL 値の扱い
SELECT SUBSTRING_INDEX(NULL, ',', 1), SUBSTRING_INDEX('test', NULL, 1);
+--------------------------------+------------------------------------+
| SUBSTRING_INDEX(NULL, ',', 1)  | SUBSTRING_INDEX('test', NULL, 1)   |
+--------------------------------+------------------------------------+
| NULL                           | NULL                               |
+--------------------------------+------------------------------------+
  1. 出現回数にゼロを指定した場合
SELECT SUBSTRING_INDEX('hello world', ' ', 0), SUBSTRING_INDEX('a,b,c', ',', 0);
+----------------------------------------+----------------------------------+
| SUBSTRING_INDEX('hello world', ' ', 0) | SUBSTRING_INDEX('a,b,c', ',', 0) |
+----------------------------------------+----------------------------------+
|                                        |                                  |
+----------------------------------------+----------------------------------+
  1. 区切り文字が存在しない場合
SELECT SUBSTRING_INDEX('hello world', ',', 1), SUBSTRING_INDEX('no-delimiter', '|', -1);
+----------------------------------------+------------------------------------------+
| SUBSTRING_INDEX('hello world', ',', 1) | SUBSTRING_INDEX('no-delimiter', '|', -1) |
+----------------------------------------+------------------------------------------+
| hello world                            | no-delimiter                             |
+----------------------------------------+------------------------------------------+
  1. 指定した回数が区切り文字の出現回数を超える場合
SELECT SUBSTRING_INDEX('a,b,c', ',', 5), SUBSTRING_INDEX('a,b,c', ',', -5);
+----------------------------------+-----------------------------------+
| SUBSTRING_INDEX('a,b,c', ',', 5) | SUBSTRING_INDEX('a,b,c', ',', -5) |
+----------------------------------+-----------------------------------+
| a,b,c                            | a,b,c                             |
+----------------------------------+-----------------------------------+
  1. UTF-8 のマルチバイト文字を区切り文字に使用する場合
SELECT SUBSTRING_INDEX('ṭṛì→ḍḍumai→hello', '→', 1), SUBSTRING_INDEX('ṭṛì→ḍḍumai→hello', '→', -1);
+-----------------------------------------------+------------------------------------------------+
| SUBSTRING_INDEX('ṭṛì→ḍḍumai→hello', '→', 1)   | SUBSTRING_INDEX('ṭṛì→ḍḍumai→hello', '→', -1)  |
+-----------------------------------------------+------------------------------------------------+
| ṭṛì                                           | hello                                          |
+-----------------------------------------------+------------------------------------------------+
  1. 複数文字の区切り文字を使用する場合
SELECT SUBSTRING_INDEX('data::field::value', '::', 2), SUBSTRING_INDEX('data::field::value', '::', -1);
+---------------------------------------------+----------------------------------------------+
| SUBSTRING_INDEX('data::field::value', '::', 2) | SUBSTRING_INDEX('data::field::value', '::', -1) |
+---------------------------------------------+----------------------------------------------+
| data::field                                 | value                                        |
+---------------------------------------------+----------------------------------------------+
  1. 抽出元の文字列が空の場合
SELECT SUBSTRING_INDEX('', ' ', 1);
+-----------------------------+
| SUBSTRING_INDEX('', ' ', 1) |
+-----------------------------+
|                             |
+-----------------------------+

キーワード​

SUBSTRING_INDEX, SUBSTRING
Report issue