カテゴリ：: 文字列関数（正規表現）

REGEXP_INSTR¶

文字列サブジェクト内の正規表現パターンの指定された出現の位置を返します。

構文¶

REGEXP_INSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <option> [ , <regexp_parameters> [ , <group_num> ] ] ] ] ] )

引数¶

必須：

subject

一致を検索する文字列です。

pattern

一致するパターンです。

パターンの指定に関するガイドラインについては、文字列関数（正規表現）をご参照ください。

オプション：

position

関数が一致の検索を開始する文字列の先頭からの文字数です。値は正の整数にする必要があります。

デフォルト： 1 （一致の検索は左側の最初の文字から始まります）

occurrence

一致を返し始めるパターンの最初の出現を指定します。

この関数は、最初の occurrence - 1 の一致をスキップします。例えば、5件の一致があり、 occurrence 引数に 3 を指定した場合、関数は最初の2件の一致を無視し、3番目、4番目、5番目の一致を返します。

デフォルト: 1

option

一致の最初の文字のオフセット（0）を返すか、一致の最後に続く最初の文字のオフセット（1）を返すかを指定します。

デフォルト： 0

regexp_parameters

一致の検索に使用されるパラメーターを指定する1つ以上の文字の文字列です。サポートされている値:


パラメーター	説明
`c`	大文字と小文字を区別する一致
`i`	大文字と小文字を区別しない一致
`m`	複数行モード
`e`	部分一致を抽出
`s`	単一行モード POSIX ワイルドカード文字 `.` 一致 `\n`

デフォルト： c

詳細については、正規表現のパラメーターの指定をご参照ください。

注釈

デフォルトでは、 REGEXP_INSTR は、件名の一致部分全体の開始または終了文字オフセットを返します。ただし、 e （「抽出」のため）パラメーターが指定されている場合、 REGEXP_INSTR は、パターンの最初のサブ式に一致するサブジェクト部分の開始文字または終了文字のオフセットを返します。e は指定されているものの、 group_num が指定されていない場合、 group_num はデフォルトで1（最初のグループ）になります。パターンにサブ式がない場合、 REGEXP_INSTR は e が設定されていないかのように動作します。e を使用する例については、このトピックの例をご参照ください。

group_num

group_num パラメーターは、抽出するグループを指定します。グループは、正規表現で括弧を使用して指定されます。

group_num が指定されている場合、Snowflakeは e オプションが指定されていなくても抽出を許可します。e オプションが暗示されています。

Snowflakeは最大1024個のグループをサポートします。

group_num を使用する例については、このトピックのキャプチャグループの例をご参照ください。

戻り値¶

NUMBER 型の値を返します。

一致するものが見つからない場合、 0 を返します。

使用上の注意¶

ポジションは0ベースではなく、1ベースです。例えば、「MAN」の文字「M」の位置は、0ではなく1です。
追加の使用上の注意については、正規表現関数の一般的な使用上の注意をご参照ください。

照合順序の詳細¶

Arguments with collation specifications currently aren't supported.

例¶

以下の例では、 REGEXP_INSTR 関数を使用しています。

基本的な例¶

テーブルを作成してデータを挿入します。

CREATE OR REPLACE TABLE demo1 (id INT, string1 VARCHAR);
INSERT INTO demo1 (id, string1) VALUES
  (1, 'nevermore1, nevermore2, nevermore3.');

一致する文字列を検索します。この場合、文字列は nevermore で1つの10進数が後に続きます（例: nevermore1）。この例では、 REGEXP_SUBSTR 関数を使って、一致する部分文字列を表示しています。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d') AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d') AS position
  FROM demo1
  ORDER BY id;

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore1 |        1 |
+----+-------------------------------------+------------+----------+

一致する文字列を検索しますが、文字列の最初の文字ではなく、文字列の5番目の文字から開始します。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d', 5) AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d', 5) AS position
  FROM demo1
  ORDER BY id;

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore2 |       13 |
+----+-------------------------------------+------------+----------+

一致する文字列を検索しますが、最初の一致ではなく3番目の一致を探します。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d', 1, 3) AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d', 1, 3) AS position
  FROM demo1
  ORDER BY id;

+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |       25 |
+----+-------------------------------------+------------+----------+

このクエリは、前のクエリとほぼ同じですが、 option 引数を使用して、一致する式の位置、または一致する式の後にある最初の文字の位置を表示する方法を示します。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore\\d', 1, 3) AS substring,
       REGEXP_INSTR( string1, 'nevermore\\d', 1, 3, 0) AS start_position,
       REGEXP_INSTR( string1, 'nevermore\\d', 1, 3, 1) AS after_position
  FROM demo1
  ORDER BY id;

+----+-------------------------------------+------------+----------------+----------------+
| ID | STRING1                             | SUBSTRING  | START_POSITION | AFTER_POSITION |
|----+-------------------------------------+------------+----------------+----------------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |             25 |             35 |
+----+-------------------------------------+------------+----------------+----------------+

このクエリは、最後の実際の出現を超えて出現を検索した場合、返される位置は0であることを示しています。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'nevermore', 1, 4) AS substring,
       REGEXP_INSTR( string1, 'nevermore', 1, 4) AS position
  FROM demo1
  ORDER BY id;

+----+-------------------------------------+-----------+----------+
| ID | STRING1                             | SUBSTRING | POSITION |
|----+-------------------------------------+-----------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | NULL      |        0 |
+----+-------------------------------------+-----------+----------+

キャプチャグループの例¶

このセクションでは、正規表現の「グループ」機能の使用方法を示します。

このセクションの最初のいくつかの例では、キャプチャグループを使用しません。このセクションは簡単な例から始まり、キャプチャグループを使用する例へと続きます。

これらの例では、以下で作成された文字列を使用します。

CREATE OR REPLACE TABLE demo2 (id INT, string1 VARCHAR);

INSERT INTO demo2 (id, string1) VALUES
    (2, 'It was the best of times, it was the worst of times.'),
    (3, 'In    the   string   the   extra   spaces  are   redundant.'),
    (4, 'A thespian theater is nearby.');

SELECT * FROM demo2;

+----+-------------------------------------------------------------+
| ID | STRING1                                                     |
|----+-------------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times.        |
|  3 | In    the   string   the   extra   spaces  are   redundant. |
|  4 | A thespian theater is nearby.                               |
+----+-------------------------------------------------------------+

文字列には以下のような特徴があります。

2 の id を含む文字列には、「the」という単語が複数回出現します。
3 の id を含む文字列には、単語「the」が複数回出現し、単語間に余分な空白があります。
4 の id を含む文字列には、複数の単語（「thespian」と「theater」）の中に「the」という文字シーケンスがありますが、「the」という単語自体はありません。

この例では、1つまたは複数の単語文字が後に続いている1つまたは複数の単語以外の文字が後に続く、単語 the の最初の出現箇所を探します。

「単語文字」には、a〜zとA〜Zの文字だけでなく、アンダースコア（「_」）と10進数0～9も含まれますが、空白、句読点などは含まれません。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+') AS substring,
       REGEXP_INSTR(string1, 'the\\W+\\w+') AS position
  FROM demo2
  ORDER BY id;

+----+-------------------------------------------------------------+--------------+----------+
| ID | STRING1                                                     | SUBSTRING    | POSITION |
|----+-------------------------------------------------------------+--------------+----------|
|  2 | It was the best of times, it was the worst of times.        | the best     |        8 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | the   string |        7 |
|  4 | A thespian theater is nearby.                               | NULL         |        0 |
+----+-------------------------------------------------------------+--------------+----------+

文字列の最初の位置から始めて、1つまたは複数の単語文字が後に続いている1つまたは複数の単語以外の文字が後に続く、2番目に出現する単語 the を探します。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+', 1, 2) AS substring,
       REGEXP_INSTR(string1, 'the\\W+\\w+', 1, 2) AS position
  FROM demo2
  ORDER BY id;

+----+-------------------------------------------------------------+-------------+----------+
| ID | STRING1                                                     | SUBSTRING   | POSITION |
|----+-------------------------------------------------------------+-------------+----------|
|  2 | It was the best of times, it was the worst of times.        | the worst   |       34 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | the   extra |       22 |
|  4 | A thespian theater is nearby.                               | NULL        |        0 |
+----+-------------------------------------------------------------+-------------+----------+

この例は前の例に似ていますが、キャプチャグループを追加します。このクエリは、一致全体の位置を返すのではなく、グループのみの位置（つまり、括弧内の正規表現の部分に一致する部分文字列の部分のみの位置）を返します。この場合、返される値は、単語 the が2番目に出現した後の単語の位置です。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2,    'e', 1) AS substring,
       REGEXP_INSTR( string1, 'the\\W+(\\w+)', 1, 2, 0, 'e', 1) AS position
  FROM demo2
  ORDER BY id;

+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

'e' （抽出）パラメーターを指定するけれども、 group_num を指定しない場合、 group_num はデフォルトで 1 になります。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2,    'e') AS substring,
       REGEXP_INSTR( string1, 'the\\W+(\\w+)', 1, 2, 0, 'e') AS position
  FROM demo2
  ORDER BY id;

+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

group_num を指定すると、パラメーターの1つとして 'e' （抽出）を指定しなかった場合でも、Snowflakeは抽出すると想定します。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2,    '', 1) AS substring,
       REGEXP_INSTR( string1, 'the\\W+(\\w+)', 1, 2, 0, '', 1) AS position
  FROM demo2
  ORDER BY id;

+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+

この例では、最初の単語が A である2単語パターンの最初、2番目、および3番目の一致から2番目の単語の位置を取得する方法を示します。これは、最後のパターンを超えようとするとSnowflakeが0を返すことも示しています。

テーブルを作成してデータを挿入します。

CREATE TABLE demo3 (id INT, string1 VARCHAR);
INSERT INTO demo3 (id, string1) VALUES
  (5, 'A MAN A PLAN A CANAL');

クエリを実行します。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 1,    'e', 1) AS substring1,
       REGEXP_INSTR( string1, 'A\\W+(\\w+)', 1, 1, 0, 'e', 1) AS position1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 2,    'e', 1) AS substring2,
       REGEXP_INSTR( string1, 'A\\W+(\\w+)', 1, 2, 0, 'e', 1) AS position2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 3,    'e', 1) AS substring3,
       REGEXP_INSTR( string1, 'A\\W+(\\w+)', 1, 3, 0, 'e', 1) AS position3,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 4,    'e', 1) AS substring4,
       REGEXP_INSTR( string1, 'A\\W+(\\w+)', 1, 4, 0, 'e', 1) AS position4
  FROM demo3;

+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+
| ID | STRING1              | SUBSTRING1 | POSITION1 | SUBSTRING2 | POSITION2 | SUBSTRING3 | POSITION3 | SUBSTRING4 | POSITION4 |
|----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------|
|  5 | A MAN A PLAN A CANAL | MAN        |         3 | PLAN       |         9 | CANAL      |        16 | NULL       |         0 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+

この例は、パターンの初出内にある最初、2番目、3番目のグループの位置を取得する方法を示しています。この場合、返される値は、単語 MAN の個々の文字の位置です。

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 1) AS substring1,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 1) AS position1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 2) AS substring2,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 2) AS position2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 3) AS substring3,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 3) AS position3
  FROM demo3;

+----+----------------------+------------+-----------+------------+-----------+------------+-----------+
| ID | STRING1              | SUBSTRING1 | POSITION1 | SUBSTRING2 | POSITION2 | SUBSTRING3 | POSITION3 |
|----+----------------------+------------+-----------+------------+-----------+------------+-----------|
|  5 | A MAN A PLAN A CANAL | M          |         3 | A          |         4 | N          |         5 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+

追加の例¶

次の例は、単語 was の出現に一致します。マッチングは文字列の最初の文字から始まり、最初の出現に続く文字の文字列内の位置を返します。

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    '\\bwas\\b',
                    1,
                    1) AS result;

+--------+
| RESULT |
|--------|
|      4 |
+--------+

次の例では、パターンに一致する文字列の一部の最初の文字のオフセットを返します。マッチングは文字列の最初の文字から始まり、パターンの最初の出現を返します。

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    'the\\W+(\\w+)',
                    1,
                    1,
                    0) AS result;

+--------+
| RESULT |
|--------|
|      8 |
+--------+

次の例は前の例と同じですが、 e パラメーターを使用して、パターンの最初のサブ式に一致するサブジェクト部分の文字オフセットを返します（the の後の最初の単語文字セット）。

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    'the\\W+(\\w+)',
                    1,
                    1,
                    0,
                    'e') AS result;

+--------+
| RESULT |
|--------|
|     12 |
+--------+

次の例は、2文字以上のアルファベット文字（大文字と小文字を区別しない）が前にある st で終わる単語の出現に一致します。マッチングは文字列の15番目の文字から始まり、最初の出現（worst の最初）に続く文字の文字列内の位置を返します。

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    '[[:alpha:]]{2,}st',
                    15,
                    1) AS result;

+--------+
| RESULT |
|--------|
|     38 |
+--------+

次の一連の例を実行するために、テーブルを作成してデータを挿入します。

CREATE OR REPLACE TABLE message(body VARCHAR(255));
INSERT INTO message VALUES
  ('Hellooo World'),
  ('How are you doing today?'),
  ('the quick brown fox jumps over the lazy dog'),
  ('PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS');

小文字 o を含む、最初に一致した最初の文字のオフセットを返します。

SELECT body,
       REGEXP_INSTR(body, '\\b\\S*o\\S*\\b') AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      1 |
| How are you doing today?                    |      1 |
| the quick brown fox jumps over the lazy dog |     11 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |      0 |
+---------------------------------------------+--------+

対象の3番目の文字から開始し、小文字の o を含む最初に一致したものの最初の文字のオフセットを返します。

SELECT body,
       REGEXP_INSTR(body, '\\b\\S*o\\S*\\b', 3) AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      3 |
| How are you doing today?                    |      9 |
| the quick brown fox jumps over the lazy dog |     11 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |      0 |
+---------------------------------------------+--------+

対象の3番目の文字から開始し、小文字の o を含む3番目に一致したものの最初の文字のオフセットを返します。

SELECT body, REGEXP_INSTR(body, '\\b\\S*o\\S*\\b', 3, 3) AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      0 |
| How are you doing today?                    |     19 |
| the quick brown fox jumps over the lazy dog |     27 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |      0 |
+---------------------------------------------+--------+

対象の3番目の文字から開始し、小文字の o を含む3番目に一致したものの最後の文字のオフセットを返します。

SELECT body, REGEXP_INSTR(body, '\\b\\S*o\\S*\\b', 3, 3, 1) AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      0 |
| How are you doing today?                    |     24 |
| the quick brown fox jumps over the lazy dog |     31 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |      0 |
+---------------------------------------------+--------+

大文字小文字を区別しない一致で、対象の3番目の文字から開始し、小文字の o を含む3番目に一致したものの最後の文字のオフセットを返します。

SELECT body, REGEXP_INSTR(body, '\\b\\S*o\\S*\\b', 3, 3, 1, 'i') AS result
  FROM message;

+---------------------------------------------+--------+
| BODY                                        | RESULT |
|---------------------------------------------+--------|
| Hellooo World                               |      0 |
| How are you doing today?                    |     24 |
| the quick brown fox jumps over the lazy dog |     31 |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     |     35 |
+---------------------------------------------+--------+