카테고리:

문자열 함수(정규식)

REGEXP_SUBSTR

문자열 내에서 정규식과 일치하는 하위 문자열을 반환합니다. 일치하는 항목이 없으면 NULL을 반환합니다.

문자열 함수(정규식) 항목도 참조하십시오.

구문

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

인자

필수:

subject

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

pattern

일치할 패턴입니다.

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

선택 사항:

position

함수가 일치 항목 검색을 시작하는 문자열 시작 부분의 문자 수입니다.

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

occurrence

패턴의 어느 발생 항목을 일치할지 지정합니다. 함수는 첫 occurrence - 1 개의 일치 항목을 건너뜁니다.

기본값: 1

regex_parameters

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

  • 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” 단어

  • 뒤에 오는 하나 이상의 비단어 문자

  • 뒤에 오는 하나 이상의 단어 문자.

“단어 문자”에는 문자 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에서 시작하여 다음 항목의 두 번째 발생을 찾습니다.

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

문자열의 위치 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   |
+----+--------+
Copy

이 예는 첫 번째 단어가 A 인, 두 단어로 된 패턴의 첫 번째, 두 번째, 세 번째 일치 항목에서 두 번째 단어를 검색하는 방법을 보여줍니다. 이는 마지막 패턴을 넘어서려고 하면 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

이 예는 패턴의 첫 번째 발생 내에서 첫 번째, 두 번째, 세 번째 그룹을 검색하는 방법을 보여줍니다. 이 경우, 반환된 값은 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 Server 액세스 로그에서 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
언어: 한국어