최종 수정: 2026년 10월 16일에

REGEXP_EXTRACT_ALL

설명

REGEXP_EXTRACT_ALL 함수는 주어진 문자열 str에 정규식 매칭을 수행하여 지정된 pattern의 첫 번째 하위 패턴과 일치하는 모든 부분을 추출하는 데 사용됩니다. 이 함수는 패턴과 일치하는 부분을 나타내는 문자열을 반환하며, 패턴은 입력 문자열 str의 일부와 정확히 일치해야 합니다. 일치하는 항목이 없거나 패턴에 하위 패턴이 없으면 빈 문자열을 반환합니다.

문자 집합 매칭을 처리할 때는 Utf-8 표준 문자 클래스를 사용해야 합니다. 이를 통해 함수가 다양한 언어의 문자를 올바르게 식별하고 처리할 수 있습니다.

'pattern'이 유효한 정규식이 아니면 오류가 발생합니다.

기본적으로 지원되는 문자 매칭 클래스: https://github.com/google/re2/wiki/Syntax

Doris는 세션 변수 enable_extended_regex(기본값: false)를 통해 전후방 탐색 영폭 어설션과 같은 고급 정규식 기능을 활성화할 수 있습니다.

세션 변수 enable_extended_regex를 true로 설정했을 때 지원되는 문자 매칭 유형: https://www.boost.org/doc/libs/latest/libs/regex/doc/html/boost_regex/syntax/perl_syntax.html

구문

REGEXP_EXTRACT_ALL(<str>, <pattern>)

매개변수

매개변수	설명
`<str>`	이 매개변수의 타입은 String입니다. 정규식 매칭을 수행할 입력 문자열을 나타냅니다. 문자열 리터럴 값이나 문자열 데이터를 포함하는 테이블의 컬럼 참조를 사용할 수 있습니다.
`<pattern>`	이 매개변수의 타입도 String입니다. 입력 문자열 과 매칭할 정규식 패턴을 지정합니다. 패턴에는 문자 클래스, 수량자, 하위 패턴과 같은 다양한 정규식 구문을 포함할 수 있습니다.

반환 값

인수 중 하나라도 NULL이면 NULL을 반환합니다.

이 함수는 지정된 정규식의 첫 번째 하위 패턴과 일치하는 입력 문자열 부분을 나타내는 문자열을 반환합니다. 반환 타입은 String입니다. 일치하는 항목이 없거나 패턴에 하위 패턴이 없으면 빈 문자열을 반환합니다.

배열을 반환하는 함수는 REGEXP_EXTRACT_ALL_ARRAY를 참조하세요.

기본 동작:

기본 설정	동작
`.`이 줄바꿈과 일치	기본적으로 `.`은 `\n`(줄바꿈)과 일치할 수 있습니다.
대소문자 구분	매칭 시 대소문자를 구분합니다.
`^`/`$`가 전체 문자열의 경계와 일치	`^`는 문자열의 시작에만, `$`는 문자열의 끝에만 일치하며 각 줄의 시작과 끝에는 일치하지 않습니다.
탐욕적 수량자	기본적으로 `*`, `+` 등은 가능한 한 많이 매칭합니다.
UTF-8	문자열을 UTF-8로 처리합니다.

패턴 수정자:

pattern 앞에 (?flags)를 붙여 기본 동작을 재정의할 수 있습니다. 여러 수정자를 조합할 수 있으며, 예를 들어 (?im)을 사용할 수 있습니다. - 접두사는 해당 옵션을 비활성화합니다. 예를 들어 (?-s)를 사용할 수 있습니다.

패턴 수정자는 기본 정규식 엔진을 사용할 때만 적용됩니다. enable_extended_regex=true를 활성화한 상태에서 영폭 어설션(예: (?<=...), (?=...))을 사용하면 쿼리는 Boost.Regex 엔진에서 처리되며, 수정자가 예상대로 동작하지 않을 수 있습니다. 두 기능을 함께 사용하지 않는 것이 좋습니다.

플래그	의미
`(?i)`	대소문자를 구분하지 않고 매칭
`(?-i)`	대소문자 구분(기본값)
`(?s)`	`.`이 줄바꿈과 일치(기본적으로 활성화됨)
`(?-s)`	`.`이 줄바꿈과 일치하지 않음
`(?m)`	여러 줄 모드: `^`가 각 줄의 시작과 일치하고, `$`가 각 줄의 끝과 일치
`(?-m)`	단일 줄 모드: `^`/`$`가 전체 문자열의 경계와 일치(기본값)
`(?U)`	비탐욕적 수량자: `*`, `+` 등이 가능한 한 적게 매칭
`(?-U)`	탐욕적 수량자(기본값): `*`, `+` 등이 가능한 한 많이 매칭

예제

'C' 전후의 소문자를 매칭하는 기본 예제입니다. 이 예제에서 패턴 ([[:lower:]]+)C([[:lower:]]+)는 하나 이상의 소문자 뒤에 'C'가 오고, 그 뒤에 다시 하나 이상의 소문자가 오는 문자열 부분과 일치합니다. 'C' 앞의 첫 번째 하위 패턴 ([[:lower:]]+)가 'b'와 일치하므로 결과는 ['b']입니다.

SELECT regexp_extract_all('AbCdE', '([[:lower:]]+)C([[:lower:]]+)');

+--------------------------------------------------------------+
| regexp_extract_all('AbCdE', '([[:lower:]]+)C([[:lower:]]+)') |
+--------------------------------------------------------------+
| ['b']                                                        |
+--------------------------------------------------------------+

문자열 내 여러 부분을 매칭하는 예제입니다. 여기서는 패턴이 문자열의 두 부분과 일치합니다. 첫 번째 매칭에서는 첫 번째 하위 패턴이 'b'와 일치하고, 두 번째 매칭에서는 첫 번째 하위 패턴이 'f'와 일치합니다. 따라서 결과는 ['b', 'f']입니다.

SELECT regexp_extract_all('AbCdEfCg', '([[:lower:]]+)C([[:lower:]]+)');

+-----------------------------------------------------------------+
| regexp_extract_all('AbCdEfCg', '([[:lower:]]+)C([[:lower:]]+)') |
+-----------------------------------------------------------------+
| ['b','f']                                                       |
+-----------------------------------------------------------------+

키-값 쌍에서 키를 추출하는 예제입니다. 패턴은 문자열의 키-값 쌍과 일치합니다. 첫 번째 하위 패턴이 키를 캡처하므로 결과는 ['abc', 'def', 'ghi']입니다.

SELECT regexp_extract_all('abc=111, def=222, ghi=333','("[^"]+"|\\w+)=("[^"]+"|\\w+)');

+--------------------------------------------------------------------------------+
| regexp_extract_all('abc=111, def=222, ghi=333', '("[^"]+"|\w+)=("[^"]+"|\w+)') |
+--------------------------------------------------------------------------------+
| ['abc','def','ghi']                                                            |
+--------------------------------------------------------------------------------+

중국어 문자를 매칭하는 예제입니다. 패턴 (\p{Han}+)(.+)는 먼저 첫 번째 하위 패턴 (\p{Han}+)으로 하나 이상의 중국어 문자와 일치하므로 결과는 ['这是一段中文']입니다.

select regexp_extract_all('这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)');

+------------------------------------------------------------------------------------------------+
| regexp_extract_all('这是一段中文 This is a passage in English 1234567', '(\p{Han}+)(.+)')       |
+------------------------------------------------------------------------------------------------+
| ['这是一段中文']                                                                               |
+------------------------------------------------------------------------------------------------+

데이터 삽입 및 REGEXP_EXTRACT_ALL 사용

CREATE TABLE test_regexp_extract_all (
    id INT,
    text_content VARCHAR(255),
    pattern VARCHAR(255)
) PROPERTIES ("replication_num"="1");


INSERT INTO test_regexp_extract_all VALUES
(1, 'apple1, banana2, cherry3', '([a-zA-Z]+)\\d'),
(2, 'red#123, blue#456, green#789', '([a-zA-Z]+)#\\d+'),
(3, 'hello@example.com, world@test.net', '([a-zA-Z]+)@');


SELECT id, regexp_extract_all(text_content, pattern) AS extracted_data
FROM test_regexp_extract_all;

+------+----------------------+
| id   | extracted_data       |
+------+----------------------+
|    1 | ['apple', 'banana', 'cherry'] |
|    2 | ['red', 'blue', 'green']     |
|    3 | ['hello', 'world']           |
+------+----------------------+

일치하는 항목이 없으면 빈 문자열을 반환합니다.

SELECT REGEXP_EXTRACT_ALL('ABC', '(\\d+)');

+-------------------------------------+
| REGEXP_EXTRACT_ALL('ABC', '(\\d+)') |
+-------------------------------------+
|                                     |
+-------------------------------------+

이모지 매칭

SELECT REGEXP_EXTRACT_ALL('👩‍💻,👨‍🚀', '(💻|🚀)');

+--------------------------------------------------------------+
| REGEXP_EXTRACT_ALL('👩‍💻,👨‍🚀', '(💻|🚀)')                      |
+--------------------------------------------------------------+
| ['💻','🚀']                                                 |
+--------------------------------------------------------------+

'Str'이 NULL이면 NULL을 반환합니다.

SELECT regexp_extract_all(NULL, '([a-z]+)');

+--------------------------------------+
| regexp_extract_all(NULL, '([a-z]+)') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+

'pattern'이 NULL이면 NULL을 반환합니다.

SELECT regexp_extract_all('Hello World', NULL);

+-----------------------------------------+
| regexp_extract_all('Hello World', NULL) |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+

모든 매개변수가 NULL이면 NULL을 반환합니다.

SELECT regexp_extract_all(NULL,NULL);

+-------------------------------+
| regexp_extract_all(NULL,NULL) |
+-------------------------------+
| NULL                          |
+-------------------------------+

pattern이 유효한 정규식이 아니면 오류가 발생합니다.

SELECT regexp_extract_all('hello (world) 123', '([[:alpha:]+');

ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[INVALID_ARGUMENT]Could not compile regexp pattern: ([[:alpha:]+
Error: missing ]: [[:alpha:]+

고급 정규식

SELECT REGEXP_EXTRACT_ALL('ID:AA-1,ID:BB-2,ID:CC-3', '(?<=ID:)([A-Z]{2}-\\d)');

ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]Invalid regex pattern: (?<=ID:)([A-Z]{2}-\d). Error: invalid perl operator: (?<

SET enable_extended_regex = true;
SELECT REGEXP_EXTRACT_ALL('ID:AA-1,ID:BB-2,ID:CC-3', '(?<=ID:)([A-Z]{2}-\\d)');

+-------------------------------------------------------------------------+
| REGEXP_EXTRACT_ALL('ID:AA-1,ID:BB-2,ID:CC-3', '(?<=ID:)([A-Z]{2}-\\d)') |
+-------------------------------------------------------------------------+
| ['AA-1','BB-2','CC-3']                                                  |
+-------------------------------------------------------------------------+

패턴 수정자

대소문자를 구분하지 않는 매칭: (?i)를 사용하면 매칭 시 대소문자를 무시합니다.

SELECT REGEXP_EXTRACT_ALL('Hello hello HELLO', '(hello)') AS case_sensitive,
       REGEXP_EXTRACT_ALL('Hello hello HELLO', '(?i)(hello)') AS case_insensitive;

+----------------+---------------------------+
| case_sensitive | case_insensitive          |
+----------------+---------------------------+
| ['hello']      | ['Hello','hello','HELLO'] |
+----------------+---------------------------+

여러 줄 모드: (?m)을 사용하면 ^와 $가 각 줄의 시작과 끝에 일치합니다.

SELECT REGEXP_EXTRACT_ALL('foo\nbar\nbaz', '^([a-z]+)') AS single_line,
       REGEXP_EXTRACT_ALL('foo\nbar\nbaz', '(?m)^([a-z]+)') AS multi_line;

+-------------+---------------------+
| single_line | multi_line          |
+-------------+---------------------+
| ['foo']     | ['foo','bar','baz'] |
+-------------+---------------------+

탐욕적 매칭과 비탐욕적 매칭: (?U)를 사용하면 수량자가 가능한 한 적게 매칭합니다.

SELECT REGEXP_EXTRACT_ALL('aXbXcXd', '(a.*X)') AS greedy,
       REGEXP_EXTRACT_ALL('aXbXcXd', '(?U)(a.*X)') AS non_greedy;

+----------+------------+
| greedy   | non_greedy |
+----------+------------+
| ['aXbXcX'] | ['aX']   |
+----------+------------+

설명​

구문​

매개변수​

반환 값​

예제​

설명

구문

매개변수

반환 값

예제