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

REGEXP_SUBSTR¶

文字列内の正規表現に一致するサブ文字列を返します。

REGEXP_SUBSTR_ALL

構文¶

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

group_num

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

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

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

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

戻り値¶

この関数は、サブ文字列に一致する型 VARCHAR の値を返します。

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

一致するものが見つかりません。
いずれかの引数が NULL です。

使用上の注意¶

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

照合順序の詳細¶

Arguments with collation specifications are currently not supported.

例¶

REGEXP_INSTR 関数のドキュメントには、 REGEXP_SUBSTR と REGEXP_INSTR の両方を使用する多くの例が含まれています。これらの例もご参照ください。

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

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;

Copy

+----+-------------------------------------------------------------+
| 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,
       REGEXP_SUBSTR(string1, 'the\\W+\\w+') AS result
  FROM demo2
  ORDER BY id;

Copy

+----+--------------+
| ID | RESULT       |
|----+--------------|
|  2 | the best     |
|  3 | the   string |
|  4 | NULL         |
+----+--------------+

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

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

Copy

+----+-------------+
| ID | RESULT      |
|----+-------------|
|  2 | the worst   |
|  3 | the   extra |
|  4 | NULL        |
+----+-------------+

一致全体を返すのではなく、「グループ」（例えば、括弧内の正規表現の部分に一致する部分文字列の部分）のみを返します。この場合、戻り値は「the」の後の単語になります。

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

Copy

+----+--------+
| ID | RESULT |
|----+--------|
|  2 | worst  |
|  3 | extra  |
|  4 | NULL   |
+----+--------+

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

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

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

Copy

クエリを実行します。

SELECT REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 1, 'e', 1) AS result1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 2, 'e', 1) AS result2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 3, 'e', 1) AS result3,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w+)', 1, 4, 'e', 1) AS result4
  FROM test_regexp_substr;

Copy

+---------+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 | RESULT4 |
|---------+---------+---------+---------|
| MAN     | PLAN    | CANAL   | NULL    |
+---------+---------+---------+---------+

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

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

Copy

+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 |
|---------+---------+---------|
| M       | A       | N       |
+---------+---------+---------+

追加の例を次に示します。

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

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');

Copy

小文字の o を含む最初の一致を返します。

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

Copy

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

対象の3番目の文字から開始し、小文字の o を含む最初の一致を返します。

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

Copy

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

対象の3番目の文字から開始し、小文字の o を含む3番目の一致を返します。

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

Copy

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

大文字小文字を区別しない一致で、対象の3番目の文字から開始し、小文字の o を含む3番目の一致を返します。

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

Copy

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

この例は、空の文字列を指定することで、正規表現パラメーターを明示的に省略できることを示しています。

SELECT body,
       REGEXP_SUBSTR(body, '(H\\S*o\\S*\\b).*', 1, 1, '') AS result
  FROM message;

Copy

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

次の例は、出現の重複を示しています。まず、テーブルを作成してデータを挿入します。

CREATE OR REPLACE TABLE overlap (
  id NUMBER,
  a STRING);

INSERT INTO overlap VALUES (1, ',abc,def,ghi,jkl,');
INSERT INTO overlap VALUES (2, ',abc,,def,,ghi,,jkl,');

SELECT * FROM overlap;

Copy

+----+----------------------+
| ID | A                    |
|----+----------------------|
|  1 | ,abc,def,ghi,jkl,    |
|  2 | ,abc,,def,,ghi,,jkl, |
+----+----------------------+

各行で次のパターンの2番目に出現するものを見つけるクエリを実行します。句読点の後に数字と文字が続き、その後に句読点が続きます。

SELECT id,
       REGEXP_SUBSTR(a,'[[:punct:]][[:alnum:]]+[[:punct:]]', 1, 2) AS result
  FROM overlap;

Copy

+----+--------+
| ID | RESULT |
|----+--------|
|  1 | ,ghi,  |
|  2 | ,def,  |
+----+--------+

次の例では、パターンの一致と連結を使用して、Apache HTTP サーバーアクセスログから JSON オブジェクトを作成します。まず、テーブルを作成してデータを挿入します。

CREATE OR REPLACE TABLE test_regexp_log (logs VARCHAR);

INSERT INTO test_regexp_log (logs) VALUES
  ('127.0.0.1 - - [10/Jan/2018:16:55:36 -0800] "GET / HTTP/1.0" 200 2216'),
  ('192.168.2.20 - - [14/Feb/2018:10:27:10 -0800] "GET /cgi-bin/try/ HTTP/1.0" 200 3395');

SELECT * from test_regexp_log

Copy

+-------------------------------------------------------------------------------------+
| LOGS                                                                                |
|-------------------------------------------------------------------------------------|
| 127.0.0.1 - - [10/Jan/2018:16:55:36 -0800] "GET / HTTP/1.0" 200 2216                |
| 192.168.2.20 - - [14/Feb/2018:10:27:10 -0800] "GET /cgi-bin/try/ HTTP/1.0" 200 3395 |
+-------------------------------------------------------------------------------------+

クエリを実行します。

SELECT '{ "ip_addr":"'
       || REGEXP_SUBSTR (logs,'\\b\\d{1,3}\.\\d{1,3}\.\\d{1,3}\.\\d{1,3}\\b')
       || '", "date":"'
       || REGEXP_SUBSTR (logs,'([\\w:\/]+\\s[+\-]\\d{4})')
       || '", "request":"'
       || REGEXP_SUBSTR (logs,'\"((\\S+) (\\S+) (\\S+))\"', 1, 1, 'e')
       || '", "status":"'
       || REGEXP_SUBSTR (logs,'(\\d{3}) \\d+', 1, 1, 'e')
       || '", "size":"'
       || REGEXP_SUBSTR (logs,'\\d{3} (\\d+)', 1, 1, 'e')
       || '"}' as Apache_HTTP_Server_Access
  FROM test_regexp_log;

Copy

+-----------------------------------------------------------------------------------------------------------------------------------------+
| APACHE_HTTP_SERVER_ACCESS                                                                                                               |
|-----------------------------------------------------------------------------------------------------------------------------------------|
| { "ip_addr":"127.0.0.1", "date":"10/Jan/2018:16:55:36 -0800", "request":"GET / HTTP/1.0", "status":"200", "size":"2216"}                |
| { "ip_addr":"192.168.2.20", "date":"14/Feb/2018:10:27:10 -0800", "request":"GET /cgi-bin/try/ HTTP/1.0", "status":"200", "size":"3395"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+