カテゴリ:

文字列関数(正規表現)

REGEXP_SUBSTR

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

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

構文

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

引数

必須:

サブジェクト

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

パターン

一致するパターン。

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

オプション:

ポジション

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

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

オカレンス

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

デフォルト: 1

正規表現パラメーター

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

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

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

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

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

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

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

デフォルト: c

注釈

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

グループ番号

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

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

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

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

戻り値

この関数は、サブ文字列に一致する型 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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+
最上部に戻る