이 예에서는 아래에 만들어진 문자열을 사용합니다.
문자열은 다음과 같은 특징이 있습니다.
다음 예제에서는 REGEXP_SUBSTR 함수를 호출합니다.
SELECT 목록에서 REGEXP_SUBSTR 함수 호출하기
패턴과 일치하는 값을 추출하거나 표시하려면 SELECT 목록에서 REGEXP_SUBSTR 함수를 호출합니다.
이 예에서는 the 단어가 처음 나오고, 그 뒤에 하나 이상의 비단어 문자(예: 단어를 구분하는 공백), 그 뒤에 하나 이상의 단어 문자가 나오는 곳을 찾습니다.
“단어 문자”에는 문자 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에서 시작하여 단어 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에서 시작하여 단어 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 인, 두 단어로 된 패턴의 첫 번째, 두 번째, 세 번째 일치 항목에서 두 번째 단어를 검색하는 방법을 보여줍니다. 이 예에서는 마지막 패턴을 넘어서려고 하면 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 |
+---------+---------+---------+---------+
이 예는 패턴의 첫 번째 발생 내에서 첫 번째, 두 번째, 세 번째 그룹을 검색하는 방법을 보여줍니다. 이 경우, 반환된 값은 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 |
+---------------------------------------------+---------+
다음과 같이 제목의 세 번째 문자부터 시작해 소문자 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 |
+---------------------------------------------+--------+
다음과 같이 제목의 세 번째 문자부터 시작해 소문자 o 를 포함하는 세 번째 일치 항목을 반환합니다.
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 |
+---------------------------------------------+--------+
다음과 같이 대/소문자를 구분하지 않고 제목의 세 번째 문자부터 시작해 소문자 o 를 포함하는 세 번째 일치 항목을 반환합니다.
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, |
+----+----------------------+
각 행에서 구두점 뒤에 숫자와 문자가 오고, 그 뒤에 구두점이 오는 두 번째 패턴을 찾는 쿼리를 실행합니다.
SELECT id,
REGEXP_SUBSTR(a,'[[:punct:]][[:alnum:]]+[[:punct:]]', 1, 2) AS result
FROM overlap;
+----+--------+
| ID | RESULT |
|----+--------|
| 1 | ,ghi, |
| 2 | ,def, |
+----+--------+
다음 예는 패턴 일치 및 연결을 사용하여 Apache HTTP Server 액세스 로그에서 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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+