SELECTリストでREGEXP_SUBSTR関数を呼びだします。
SELECTリストでREGEXP_SUBSTR関数を呼び出して、パターンに一致する値を抽出または表示します。
この例では、単語``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つまたは複数の単語文字が後に続いている1つまたは複数の単語以外の文字が後に続く、2番目に出現する単語 the を探します。
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つまたは複数の単語文字が後に続いている1つまたは複数の単語以外の文字が後に続く、2番目に出現する単語 the を探します。
一致全体を返すのではなく、「グループ」（例えば、括弧内の正規表現の部分に一致する部分文字列の部分）のみを返します。この場合、戻り値は「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 OR REPLACE TABLE test_regexp_substr (string1 VARCHAR);;
INSERT INTO test_regexp_substr (string1) VALUES ('A MAN A PLAN A CANAL');
クエリを実行します。
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;
+---------+---------+---------+---------+
| 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;
+---------+---------+---------+
| 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');
単語の境界（\b）、その後に0個以上の単語文字（\S）、文字``o``、次の単語の境界までの0個以上の単語文字をマッチングして、``o``を含む最初の一致を返します。
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 |
+---------------------------------------------+---------+
対象の3番目の文字から開始し、小文字の o を含む最初の一致を返します。
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 |
+---------------------------------------------+--------+
対象の3番目の文字から開始し、小文字の o を含む3番目の一致を返します。
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 |
+---------------------------------------------+--------+
大文字小文字を区別しない一致で、対象の3番目の文字から開始し、小文字の o を含む3番目の一致を返します。
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 |
+---------------------------------------------+--------------------------+
次の例は、出現の重複を示しています。まず、テーブルを作成してデータを挿入します。
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;
+----+----------------------+
| 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;
+----+--------+
| 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
+-------------------------------------------------------------------------------------+
| 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;
+-----------------------------------------------------------------------------------------------------------------------------------------+
| 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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+