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

REGEXP_SUBSTR_ALL¶

文字列内の正規表現に一致するすべてのサブ文字列が含まれる、 ARRAY を返します。

エイリアス:: REGEXP_EXTRACT_ALL

REGEXP_SUBSTR

構文¶

REGEXP_SUBSTR_ALL( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <regex_parameters> [ , <group_num> ] ] ] ] )

Copy

引数¶

必須：

subject

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

pattern

一致するパターン。

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

オプション:

position

関数が一致の検索を開始する文字列の先頭からの文字数です。

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

occurrence

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

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

デフォルト: 1

regex_parameters

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

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

デフォルト: c

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

注釈

デフォルトでは、 REGEXP_SUBSTR_ALL は件名の一致部分全体を返します。ただし、 e パラメーターが指定されている場合、 REGEXP_SUBSTR_ALL はパターンの最初のグループに一致する対象の一部を返します。 e は指定されているものの、 group_num が指定されていない場合、 group_num はデフォルトで1（最初のグループ）になります。パターンにサブ式がない場合、 REGEXP_SUBSTR_ALL は e が設定されていないかのように動作します。 e を使用する例については、このトピックの例をご参照ください。

group_num

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

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

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

group_num を使用する例については、このトピックの例をご参照ください。

戻り値¶

この関数は、 ARRAY 型の値を返します。配列には、一致する各サブ文字列の要素が含まれています。

一致するものが見つからない場合、関数は空の配列を返します。

この関数は以下の場合に NULL を返します。

いずれかの引数が NULL です。
group_num を指定し、パターンはその番号を使用してグループ化を指定しません。例えば、パターンが1つのグループ（例えば、 a(b)c）のみを指定し、 2 を group_num として使用する場合、関数は NULL を返します。

使用上の注意¶

正規表現の使用に関する追加情報については、文字列関数（正規表現）をご参照ください。

照合順序の詳細¶

Arguments with collation specifications are currently not supported.

例¶

次の例のパターンは、小文字の「a」とそれに続く数字に一致します。この例では、一致すべてを含む ARRAY が返されます。

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]') AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a1", |
|   "a2", |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

次の例では、文字列（2）の2番目の文字から一致の検索を開始します。

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 2) AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a2", |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

次の例では、文字列（3）にあるパターンの3番目の発生から一致を返し始めます。

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 1, 3) AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a3", |
|   "a4", |
|   "a6"  |
| ]       |
+---------+

次の例では、大文字と小文字を区別しない一致（i）を実行します。

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'a[[:digit:]]', 1, 1, 'i') AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a1", |
|   "a2", |
|   "a3", |
|   "a4", |
|   "A5", |
|   "a6"  |
| ]       |
+---------+

次の例では、大文字と小文字を区別しない一致を実行し、最初のグループ（ie）に一致する文字列の部分を返します。

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', '(a)([[:digit:]])', 1, 1, 'ie') AS matches;

Copy

+---------+
| MATCHES |
|---------|
| [       |
|   "a",  |
|   "a",  |
|   "a",  |
|   "a",  |
|   "A",  |
|   "a"   |
| ]       |
+---------+

次の例は、一致するものが見つからない場合に関数が空の配列を返すことを示しています。

SELECT REGEXP_SUBSTR_ALL('a1_a2a3_a4A5a6', 'b') AS matches;

Copy

+---------+
| MATCHES |
|---------|
| []      |
+---------+

この例では、最初の単語が A である2単語パターンの最初、2番目、3番目の一致から文字列内の2番目の各単語を取得する方法を示します。

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

CREATE OR REPLACE TABLE test_regexp_substr_all (string1 VARCHAR);;
INSERT INTO test_regexp_substr_all (string1) VALUES ('A MAN A PLAN A CANAL');

Copy

クエリを実行します。

SELECT REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 2, 'e', 1) AS result2,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w+)', 1, 3, 'e', 1) AS result3
  FROM test_regexp_substr_all;

Copy

+-----------+-----------+-----------+
| RESULT1   | RESULT2   | RESULT3   |
|-----------+-----------+-----------|
| [         | [         | [         |
|   "MAN",  |   "PLAN", |   "CANAL" |
|   "PLAN", |   "CANAL" | ]         |
|   "CANAL" | ]         |           |
| ]         |           |           |
+-----------+-----------+-----------+

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

SELECT REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 2) AS result2,
       REGEXP_SUBSTR_ALL(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 'e', 3) AS result3
  FROM test_regexp_substr_all;

Copy

+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 |
|---------+---------+---------|
| [       | [       | [       |
|   "M",  |   "A",  |   "N",  |
|   "P",  |   "L",  |   "A",  |
|   "C"   |   "A"   |   "N"   |
| ]       | ]       | ]       |
+---------+---------+---------+