カテゴリ:

文字列関数(正規表現)

REGEXP_SUBSTR

文字列内の正規表現に一致するサブ文字列を返します。一致するものが見つからない場合、 NULLを返します。

文字列関数(正規表現) もご参照ください。

構文

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

引数

必須:

subject

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

pattern

一致するパターン。

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

オプション:

position

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

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

occurrence

一致するパターンの出現を指定します。この関数は、最初の occurrence - 1 の一致をスキップします。

デフォルト: 1

regex_parameters

一致の検索に使用される正規表現パラメーターを指定する1つ以上の文字の文字列です。サポートされている値は次のとおりです。

  • c :大文字と小文字を区別します。

  • i :大文字と小文字を区別しません。

  • m :複数行モードです。

  • e :部分一致を抽出します。

  • s :「ワイルドカード」は改行にも一致します。

詳細については、 正規表現パラメーター のドキュメントをご参照ください。

デフォルト: c

注釈

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

group_num

group_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.')
    ;
Copy

次の例は以下を探します。

  • 「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         |
+----+--------------+
Copy

文字列の位置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        |
+----+-------------+
Copy

文字列の位置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   |
+----+--------+
Copy

この例では、最初の単語が 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')
    ;
Copy
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    |
+----+---------+---------+---------+---------+
Copy

この例は、パターンの最初の出現内で最初、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       |
+----+---------+---------+---------+
Copy

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

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

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

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                                               |
+---------------------------------------------+----------------------------------------------------+
Copy
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                                                 |
+---------------------------------------------+------------------------------------------------------+
Copy

次の例は、発生の重複を示しています。

-- 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,  |
+----+--------+
Copy

次の例では、パターンマッチングと連結を使用して、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');
Copy
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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+
Copy