- カテゴリ：
REGEXP_SUBSTR¶
文字列内の正規表現に一致するサブ文字列を返します。一致するものが見つからない場合、 NULLを返します。
文字列関数（正規表現） もご参照ください。
構文¶
REGEXP_SUBSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <regex_parameters> [ , <group_num> ] ] ] ] )
引数¶
必須：
subject一致を検索する文字列です。
pattern一致するパターン。
パターンの指定に関するガイドラインについては、 文字列関数（正規表現） をご参照ください。
オプション:
position関数が一致の検索を開始する文字列の先頭からの文字数です。
デフォルト：
1（一致の検索は左側の最初の文字から始まります）occurrence一致するパターンの出現を指定します。この関数は、最初の
occurrence - 1の一致をスキップします。デフォルト：
1regex_parameters一致の検索に使用される正規表現パラメーターを指定する1つ以上の文字の文字列です。サポートされている値は次のとおりです。
c：大文字と小文字を区別します。i：大文字と小文字を区別しません。m：複数行モードです。e：部分一致を抽出します。s：「ワイルドカード」は改行にも一致します。
詳細については、 正規表現パラメーター のドキュメントをご参照ください。
デフォルト：
c注釈
デフォルトでは、 REGEXP_SUBSTR は件名の一致部分全体を返します。ただし、
e（「抽出」のため）パラメーターが指定されている場合、 REGEXP_SUBSTR はパターンの最初のグループに一致するサブジェクトの部分を返します。eは指定されているものの、group_numが指定されていない場合、group_numはデフォルトで1（最初のグループ）になります。パターンにサブ式がない場合、 REGEXP_SUBSTR はeが設定されていないかのように動作します。eを使用する例については、このトピックの 例 をご参照ください。group_numgroup_numパラメーターは、抽出するグループを指定します。グループは、正規表現で括弧を使用して指定されます。group_numが指定されている場合、Snowflakeは'e'オプションが指定されていなくても抽出を許可します。'e'が暗黙的に示されます。Snowflakeは最大1024個のグループをサポートします。
group_numを使用する例については、このトピックの 例 をご参照ください。
戻り値¶
この関数は、サブ文字列に一致する型 VARCHAR の値を返します。
使用上の注意¶
正規表現の使用に関する追加情報については、 文字列関数（正規表現） をご参照ください。
照合の詳細¶
Arguments with collation specifications are currently not supported.
例¶
REGEXP_INSTR 関数のドキュメントには、 REGEXP_SUBSTR と REGEXP_INSTR の両方を使用する多くの例が含まれています。これらの例もご参照ください。
次のいくつかの例では、以下で作成された文字列のテーブルを使用します。
CREATE TABLE demo2 (id INT, string1 VARCHAR);; INSERT INTO demo2 (id, string1) VALUES -- A string with multiple occurrences of the word "the". (2, 'It was the best of times, it was the worst of times.'), -- A string with multiple occurrences of the word "the" and with extra -- blanks between words. (3, 'In the string the extra spaces are redundant.'), -- A string with the character sequence "the" inside multiple words -- ("thespian" and "theater"), but without the word "the" by itself. (4, 'A thespian theater is nearby.') ;
次の例は以下を探します。
「the」という単語
1つ以上の非単語文字が続く
1つ以上の単語文字が続く。
「単語文字」には、a〜zとA〜Zの文字だけでなく、アンダースコア（「_」）と10進数字0～9も含まれますが、空白、句読点などは含まれません。
select id, regexp_substr(string1, 'the\\W+\\w+') as "RESULT" from demo2 order by id; +----+--------------+ | ID | RESULT | |----+--------------| | 2 | the best | | 3 | the string | | 4 | NULL | +----+--------------+
文字列の位置1から開始して、以下の2番目の出現を探します。
「the」という単語
1つ以上の非単語文字が続く
1つ以上の単語文字が続く。
select id, regexp_substr(string1, 'the\\W+\\w+', 1, 2) as "RESULT" from demo2 order by id; +----+-------------+ | ID | RESULT | |----+-------------| | 2 | the worst | | 3 | the extra | | 4 | NULL | +----+-------------+
文字列の位置1から開始して、以下の2番目の出現を探します。
「the」という単語
1つ以上の非単語文字が続く
1つ以上の単語文字が続く。
一致全体を返すのではなく、「グループ」（つまり、括弧内の正規表現の部分に一致する部分文字列の部分）のみを返します。この場合、戻り値は「the」の後の単語になります。
select id, regexp_substr(string1, 'the\\W+(\\w+)', 1, 2, 'e', 1) as "RESULT" from demo2 order by id; +----+--------+ | ID | RESULT | |----+--------| | 2 | worst | | 3 | extra | | 4 | NULL | +----+--------+
この例では、最初の単語が A である2単語パターンの最初、2番目、3番目の一致から2番目の単語を取得する方法を示します。これは、最後のパターンの範囲を超えようとすると、Snowflakeが NULL を返すことも示しています。
CREATE TABLE demo3 (id INT, string1 VARCHAR);; INSERT INTO demo3 (id, string1) VALUES (5, 'A MAN A PLAN A CANAL') ;select id, 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 demo3; +----+---------+---------+---------+---------+ | ID | RESULT1 | RESULT2 | RESULT3 | RESULT4 | |----+---------+---------+---------+---------| | 5 | MAN | PLAN | CANAL | NULL | +----+---------+---------+---------+---------+
この例は、パターンの最初の出現内で最初、2番目、3番目のグループを取得する方法を示しています。この場合、返される値は、単語 MAN の個々の文字です。
select id, 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 demo3; +----+---------+---------+---------+ | ID | RESULT1 | RESULT2 | RESULT3 | |----+---------+---------+---------| | 5 | M | A | N | +----+---------+---------+---------+
追加の例を次に示します。
-- Prepare example
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');
SELECT body, REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b') AS result FROM message;
+---------------------------------------------+------------------------------------------+
| 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 |
+---------------------------------------------+------------------------------------------+
SELECT body, REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3) AS result FROM message;
+---------------------------------------------+-------------------------------------------+
| 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 |
+---------------------------------------------+-------------------------------------------+
SELECT body, REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3, 3) AS result FROM message;
+---------------------------------------------+----------------------------------------------+
| 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 |
+---------------------------------------------+----------------------------------------------+
SELECT body, REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b', 3, 3, 'i') AS result FROM message;
+---------------------------------------------+---------------------------------------------------+
| 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;
+---------------------------------------------+----------------------------------------------------+
| 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 |
+---------------------------------------------+----------------------------------------------------+
SELECT body, REGEXP_SUBSTR(body, '(H\\S*o\\S*\\b) .*', 1, 1, 'e') AS result FROM message;
+---------------------------------------------+------------------------------------------------------+
| BODY | result |
|---------------------------------------------+------------------------------------------------------|
| Hellooo World | Hellooo |
| How are you doing today? | How |
| the quick brown fox jumps over the lazy dog | NULL |
| PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS | NULL |
+---------------------------------------------+------------------------------------------------------+
次の例は、発生の重複を示しています。
-- Prepare example 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; select id, regexp_substr(a,'[[:punct:]][[:alnum:]]+[[:punct:]]', 1, 2) as result from overlap; +----+--------+ | ID | result | |----+--------| | 1 | ,ghi, | | 2 | ,def, | +----+--------+
次の例では、パターンマッチングと連結を使用して、Apache HTTP サーバーアクセスログから JSON オブジェクトを作成します。
-- Prepare example CREATE OR REPLACE TABLE log (logs varchar); INSERT INTO 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 '{ "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 log; +-----------------------------------------------------------------------------------------------------------------------------------------+ | 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"} | +-----------------------------------------------------------------------------------------------------------------------------------------+