REGEXP_SUBSTR

REGEXP_SUBSTR

REGEXP_SUBSTR은 문자열 안에서 정규식(regular expression)과 일치하는 부분 문자열을 반환하는 함수예요. 특정 패턴에 맞는 문자열 조각을 추출하거나 행을 필터링할 때 사용해요.

출처: Snowflake SQL Reference

본문

카테고리: String 함수 (정규식)

구문 (Syntax)

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

인자 (Arguments)

필수:

<subject> — 일치 항목을 검색할 문자열이에요.

<pattern> — 일치시킬 패턴이에요. 패턴 지정 지침은 String 함수 (정규식)를 참고해요.

선택:

<position> — 함수가 일치 항목을 검색하기 시작하는, 문자열 시작부터의 문자 수예요. 값은 양의 정수여야 해요.

기본값: 1 (가장 왼쪽 첫 문자에서 일치 검색 시작)

<occurrence> — 일치 항목 반환을 시작할 패턴의 첫 번째 발생을 지정해요. 함수는 처음 occurrence - 1개의 일치 항목을 건너뛰어요. 예를 들어 일치 항목이 5개 있고 occurrence 인자에 3을 지정하면, 함수는 처음 두 일치 항목을 무시하고 세 번째·네 번째·다섯 번째 일치 항목을 반환해요.

기본값: 1

<regex_parameters> — 일치 항목 검색에 사용되는 매개변수를 지정하는 하나 이상의 문자로 된 문자열이에요. 지원되는 값:

매개변수	설명
c	대소문자 구분 일치
i	대소문자 구분 없는 일치
m	여러 줄(multi-line) 모드
e	하위 일치(submatch) 추출
s	한 줄(single-line) 모드. POSIX 와일드카드 문자 .가 \n과 일치

기본값: c

자세한 내용은 정규식 매개변수 지정을 참고해요.

참고: 기본적으로 REGEXP_SUBSTR은 subject의 일치하는 전체 부분을 반환해요. 하지만 e(extract) 매개변수를 지정하면 REGEXP_SUBSTR은 패턴의 첫 번째 그룹과 일치하는 subject 부분을 반환해요. e를 지정했는데 group_num도 지정하지 않았다면 group_num은 1(첫 번째 그룹)로 기본 설정돼요. 패턴에 하위 표현식이 없으면 REGEXP_SUBSTR은 e가 설정되지 않은 것처럼 동작해요. e를 사용하는 예시는 이 문서의 예시를 참고해요.

<group_num> — 추출할 그룹을 지정해요. 그룹은 정규식에서 괄호를 사용해 지정해요. group_num이 지정되면 'e' 옵션을 함께 지정하지 않아도 Snowflake가 추출을 허용해요. 'e'는 암시적으로 적용돼요. Snowflake는 최대 1024개 그룹을 지원해요. group_num을 사용하는 예시는 이 문서의 예시를 참고해요.

반환값 (Returns)

일치하는 부분 문자열인 VARCHAR 타입의 값을 반환해요. 2026_04 BCR bundle이 활성화되고 subject에 대조(collation)가 지정된 경우, subject의 대조가 반환 값으로 전파돼요. 대조가 없는 결과를 얻으려면 subject에 COLLATE ''를 적용해요.

다음의 경우 함수는 NULL을 반환해요:

• 일치 항목이 없을 때
• 인자 중 하나라도 NULL일 때

사용 시 유의사항 (Usage notes)

정규식 사용에 대한 추가 정보는 String 함수 (정규식)을 참고해요.

대조(collation) 세부 사항

2026_04 BCR bundle이 활성화되면 함수는 대조 지정이 있는 인자를 받아들여요. 대조는 패턴 일치에 영향을 주지 않아요. parameters 인자에 i 플래그를 전달하지 않는 한 일치는 항상 대소문자를 구분해요.

예시 (Examples)

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;

+----+-------------------------------------------------------------+
| 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라는 단어의 첫 번째 발생을 찾고, 그 뒤에 하나 이상의 비단어(non-word) 문자(예: 단어를 구분하는 공백), 그 뒤에 하나 이상의 단어 문자를 찾아요. "단어 문자(word characters)"에는 a-z, A-Z 문자뿐 아니라 밑줄("_")과 십진 숫자 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    |
+---------------------------------------------+---------+

subject의 세 번째 문자에서 시작해 소문자 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   |
+---------------------------------------------+--------+

subject의 세 번째 문자에서 시작해 소문자 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   |
+---------------------------------------------+--------+

subject의 세 번째 문자에서 시작해, 대소문자 구분 없이 일치시키면서 소문자 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                     |
+---------------------------------------------+--------------------------+

다음 예시는 겹치는 발생(overlapping occurrences)을 보여줘요. 먼저 테이블을 만들고 데이터를 삽입해요:

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

다음 예시는 패턴 일치와 결합(concatenation)을 사용해 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"} |
+-----------------------------------------------------------------------------------------------------------------------------------------+

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;

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

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

더 알아보기

• String 함수 (정규식)
• REGEXP_INSTR