카테고리:: 문자열 함수(정규식)

REGEXP_SUBSTR¶

문자열 내에서 :doc:`정규식</sql-reference/functions-regexp>`과 일치하는 하위 문자열을 반환합니다.

구문¶

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

Copy

인자¶

필수:

subject

일치 항목을 검색할 문자열입니다.

pattern

일치할 패턴입니다.

패턴 지정에 관한 지침은 문자열 함수(정규식) 섹션을 참조하십시오.

선택 사항:

position

함수가 일치 항목 검색을 시작하는 문자열 시작 부분의 문자 수입니다. 값은 양의 정수여야 합니다.

기본값: 1 (일치 항목 검색은 왼쪽의 첫 번째 문자에서 시작됨)

occurrence

일치 항목을 반환하기 시작할 첫 번째 패턴 항목을 지정합니다.

함수는 첫 occurrence - 1 개의 일치 항목을 건너뜁니다. 예를 들어 5개의 일치 항목이 있고 occurrence 인자에 대해 3 을 지정할 경우 이 함수는 처음 두 일치 항목은 무시하고 세 번째, 네 번째, 다섯 번째 일치 항목을 반환합니다.

기본값: 1

regex_parameters

일치 항목을 검색하는 데 사용되는 매개 변수를 지정하는 하나 이상의 문자로 된 문자열입니다. 지원되는 값:

매개 변수	설명
`c`	대/소문자를 구분하는 일치
`i`	대/소문자를 구분하지 않는 일치
`m`	여러 줄 모드
`e`	하위 일치 항목 추출
`s`	한 줄 모드 POSIX 와일드카드 문자 `.` 는 `\n` 과 일치합니다.

기본값: c

자세한 내용은 정규식에 매개 변수 지정하기 섹션을 참조하십시오.

참고

기본적으로 REGEXP_SUBSTR은 주제에서 일치하는 전체 부분을 반환합니다. 그러나 e (“추출”의 경우) 매개 변수가 지정되면 REGEXP_SUBSTR은 패턴의 첫 번째 그룹과 일치하는 주제 부분을 반환합니다. e 가 지정되었지만 group_num 도 지정되지 않은 경우, group_num 의 기본값은 1(첫 번째 그룹)입니다. 패턴에 하위 식이 없으면 REGEXP_SUBSTR은 e 가 설정되지 않은 것처럼 작동합니다. e 를 사용하는 예는 이 항목의 예 를 참조하십시오.

group_num

추출할 그룹을 지정합니다. 그룹은 정규식에서 괄호를 사용하여 지정됩니다.

group_num 가 지정되면 Snowflake는 추출을 허용합니다. 'e' 옵션도 지정하지 않은 경우에도 마찬가지입니다. 'e' 는 내포되어 있습니다.

Snowflake는 최대 1024개의 그룹을 지원합니다.

group_num 를 사용하는 예는 이 항목의 예 를 참조하십시오.

반환¶

이 함수는 일치하는 부분 문자열인 VARCHAR 타입의 값을 반환합니다.

다음의 경우 함수는 NULL을 반환합니다.

일치하는 항목이 없는 경우.
NULL인 인지가 있는 경우.

사용법 노트¶

정규식 사용에 대한 추가 정보는 문자열 함수(정규식) 섹션을 참조하십시오.

데이터 정렬 세부 정보¶

Arguments with collation specifications currently aren’t supported.

예¶

REGEXP_INSTR 함수 설명서에는 REGEXP_SUBSTR 및 REGEXP_INSTR을 모두 사용하는 많은 예가 포함되어 있습니다. 이러한 예도 살펴보는 것이 좋습니다.

이 예에서는 아래에 만들어진 문자열을 사용합니다.

CREATE OR REPLACE TABLE demo2 (id INT, string1 VARCHAR);

INSERT INTO demo2 (id, string1) VALUES
    (2, 'It was the best of times, it was the worst of times.'),
    (3, 'In    the   string   the   extra   spaces  are   redundant.'),
    (4, 'A thespian theater is nearby.');

SELECT * FROM demo2;

Copy

+----+-------------------------------------------------------------+
| ID | STRING1                                                     |
|----+-------------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times.        |
|  3 | In    the   string   the   extra   spaces  are   redundant. |
|  4 | A thespian theater is nearby.                               |
+----+-------------------------------------------------------------+

문자열은 다음과 같은 특징이 있습니다.

id 가 2 인 문자열에는 “the”라는 단어가 여러 번 나옵니다.
id 가 3 인 문자열에는 “the”라는 단어가 여러 번 나오고 단어 사이에 공백이 추가로 있습니다.
id 가 4 인 문자열에는 여러 단어(“thespian”과 “theater”) 내에 문자 시퀀스 “the”가 있지만, 그 자체로 “the”라는 단어는 없습니다.

다음 예제에서는 REGEXP_SUBSTR 함수를 호출합니다.

SELECT 목록에서 REGEXP_SUBSTR 함수 호출하기
WHERE 절에서 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;

Copy

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

Copy

+----+-------------+
| ID | RESULT      |
|----+-------------|
|  2 | the worst   |
|  3 | the   extra |
|  4 | NULL        |
+----+-------------+

전체 일치를 반환하는 대신 “그룹”(예: 괄호 안의 정규식 부분과 일치하는 하위 문자열 부분)만 반환합니다. 이 경우, 반환된 값은 “the” 뒤에 오는 단어일 것입니다.

SELECT id,
       REGEXP_SUBSTR(string1, 'the\\W+(\\w+)', 1, 2, 'e', 1) AS result
  FROM demo2
  ORDER BY id;

Copy

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

Copy

쿼리를 실행합니다.

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;

Copy

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

Copy

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

Copy

단어 경계(\b)와 일치시킨 후, 0개 이상의 단어 문자(\S), 문자 o, 그리고 다음 단어 경계까지 0개 이상의 단어 문자를 일치시켜 소문자 ``o``을 포함하는 첫 번째 일치 항목을 반환합니다.

SELECT body,
       REGEXP_SUBSTR(body, '\\b\\S*o\\S*\\b') AS result
  FROM message;

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

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

Copy

+-----------------------------------------------------------------------------------------------------------------------------------------+
| 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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+

WHERE 절에서 REGEXP_SUBSTR 함수 호출하기¶

WHERE 절에서 REGEXP_SUBSTR 함수를 호출하여 패턴과 일치하는 값이 포함된 행을 필터링합니다. 이 함수를 사용하면 OR 조건을 여러 개 사용하는 것을 피할 수 있습니다.

다음 예제에서는 이전에 만든 demo2 테이블을 쿼리하여 best 문자열 또는 thespian 문자열을 포함하는 행을 반환합니다. 패턴과 일치하는 행을 반환하는 조건에 ``IS NOT NULL``을 추가합니다. 즉, REGEXP_SUBSTR 함수가 ``NULL``을 반환하지 않은 행은 다음과 같습니다.

SELECT id, string1
  FROM demo2
  WHERE REGEXP_SUBSTR(string1, '(best|thespian)') IS NOT NULL;

Copy

+----+------------------------------------------------------+
| ID | STRING1                                              |
|----+------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times. |
|  4 | A thespian theater is nearby.                        |
+----+------------------------------------------------------+

AND 조건을 사용하여 여러 패턴과 일치하는 행을 찾을 수 있습니다. 예를 들어, 다음 쿼리는 best 문자열 또는 thespian 문자열을 포함하고 It 문자열로 시작하는 행을 반환합니다.

SELECT id, string1
  FROM demo2
  WHERE REGEXP_SUBSTR(string1, '(best|thespian)') IS NOT NULL
    AND REGEXP_SUBSTR(string1, '^It') IS NOT NULL;

Copy

+----+------------------------------------------------------
| ID | STRING1                                              |
|----+------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times. |
+----+------------------------------------------------------+