REGEXP_EXTRACT_OR_NULL
설명
텍스트 문자열에서 대상 정규 표현식 패턴과 일치하는 첫 번째 부분 문자열을 찾고, 정규 표현식 그룹 인덱스에 따라 그 안에서 특정 그룹을 추출합니다.
문자 집합 매칭을 처리할 때는 Utf-8 표준 문자 클래스를 사용해야 합니다. 이렇게 하면 함수가 여러 언어의 다양한 문자를 올바르게 식별하고 처리할 수 있습니다.
Apache Doris 3.0.2부터 지원됩니다.
'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_OR_NULL(<str>, <pattern>, <pos>)
매개변수
| 매개변수 | 설명 |
|---|---|
<str> | 문자열 매개변수입니다. 정규 표현식 매칭을 수행할 텍스트 문자열을 나타냅니다. 이 문자열에는 어떤 문자 조합이든 포함할 수 있으며, 함수는 문자열 안에서 |
<pattern> | 문자열 매개변수입니다. 대상 정규 표현식 패턴을 나타냅니다. 이 패턴에는 다양한 정규 표현식 메타문자와 문자 클래스를 포함할 수 있으며, 이를 통해 매칭할 부분 문자열의 규칙을 정확하게 정의합니다. |
<pos> | 정수 매개변수입니다. 추출할 정규 표현식 그룹의 인덱스를 나타냅니다. 인덱스는 1부터 시작합니다. |
반환 값
문자열 타입을 반환하며, 결과는 <pattern>과 일치하는 부분입니다.
입력 <pos>가 0이면 첫 번째로 일치하는 부분 문자열 전체를 반환합니다.
입력 <pos>가 유효하지 않으면(음수이거나 정규 표현식 그룹 수를 초과하면) NULL을 반환합니다.
정규 표현식과 일치하는 부분이 없으면 NULL을 반환합니다.
<pos> < 0이면 NULL을 반환합니다.
pos가 <str>의 길이보다 크면 NULL을 반환합니다.
기본 동작:
| 기본 설정 | 동작 |
|---|---|
.이 줄 바꿈 문자와 일치 | .은 기본적으로 \n(줄 바꿈 문자)과 일치할 수 있습니다. |
| 대소문자 구분 | 매칭 시 대소문자를 구분합니다. |
^/$가 전체 문자열의 경계와 일치 | ^는 문자열의 시작에만, $는 문자열의 끝에만 일치하며 각 줄의 시작과 끝에는 일치하지 않습니다. |
| 탐욕적 수량자 | *, + 등은 기본적으로 가능한 한 많이 매칭합니다. |
| UTF-8 | 문자열을 UTF-8로 처리합니다. |
패턴 수정자:
pattern 앞에 (?flags)를 붙여 기본 동작을 변경할 수 있습니다. 여러 수정자를 조합할 수 있습니다(예: (?im)). - 접두사는 해당 옵션을 비활성화합니다(예: (?-s)).
패턴 수정자는 기본 정규 표현식 엔진을 사용할 때만 적용됩니다. enable_extended_regex=true가 활성화된 상태에서 영폭 어설션(예: (?<=...), (?=...))을 사용하면 Boost.Regex 엔진이 쿼리를 처리하므로 수정자가 예상대로 동작하지 않을 수 있습니다. 두 기능을 함께 사용하지 않는 것이 좋습니다.
| 플래그 | 의미 |
|---|---|
(?i) | 대소문자를 구분하지 않고 매칭 |
(?-i) | 대소문자를 구분하여 매칭(기본값) |
(?s) | .이 줄 바꿈 문자와 일치(기본적으로 활성화됨) |
(?-s) | .이 줄 바꿈 문자와 일치하지 않음 |
(?m) | 여러 줄 모드: ^는 각 줄의 시작, $는 각 줄의 끝과 일치 |
(?-m) | 한 줄 모드: ^/$가 전체 문자열의 경계와 일치(기본값) |
(?U) | 비탐욕적 수량자: *, + 등이 가능한 한 적게 매칭 |
(?-U) | 탐욕적 수량자(기본값): *, + 등이 가능한 한 많이 매칭 |
예제
일치하는 부분에서 특정 그룹을 추출합니다. 설명: 정규 표현식 ([[:lower:]]+)C([[:lower:]]+)는 'C'로 구분된 하나 이상의 소문자로 이루어진 연속 문자열을 찾습니다. 인덱스가 1인 그룹은 첫 번째 소문자 연속 문자열에 해당하므로 'b'를 반환합니다.
SELECT REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 1);
+---------------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 1) |
+---------------------------------------------------------------------------+
| b |
+---------------------------------------------------------------------------+
일치하는 부분 문자열 전체를 반환합니다.
SELECT REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 0);
+---------------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 0) |
+---------------------------------------------------------------------------+
| bCd |
+---------------------------------------------------------------------------+
유효하지 않은 그룹 인덱스입니다. 패턴에는 그룹이 2개뿐이므로 인덱스 5는 범위를 벗어나 NULL을 반환합니다.
SELECT REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 5);
+---------------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[:lower:]]+)', 5) |
+---------------------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------------------+
정규 표현식과 일치하는 부분이 없는 경우입니다. 문자열 'AbCdE'에는 패턴과 완전히 일치하는 부분이 없습니다.
SELECT REGEXP_EXTRACT_OR_NULL('AbCdE', '([[:lower:]]+)C([[:upper:]]+)', 1);
+---------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('AbCdE', '([[:lower:]]+)C([[:upper:]]+)', 1) |
+---------------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------------+
중국어 문자를 매칭합니다. 패턴 (\p{Han}+)(.+)는 먼저 하나 이상의 중국어 문자에 매칭한 다음, 나머지 모든 문자에 매칭합니다. 인덱스가 2인 그룹은 중국어 문자 뒤에 오는 문자열에서 중국어가 아닌 부분을 나타냅니다.
select REGEXP_EXTRACT_OR_NULL('这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)', 2);
+---------------------------------------------------------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('这是一段中文 This is a passage in English 1234567', '(\\p{Han}+)(.+)', 2) |
+---------------------------------------------------------------------------------------------------------+
| This is a passage in English 1234567 |
+---------------------------------------------------------------------------------------------------------+
테이블에 데이터를 삽입하고 추출을 수행합니다.
CREATE TABLE test_regexp_extract_or_null (
id INT,
text_column VARCHAR(255),
pattern_column VARCHAR(255),
position_column INT
) PROPERTIES ("replication_num"="1");
INSERT INTO test_regexp_extract_or_null VALUES
(1, 'abc123def', '([a-z]+)([0-9]+)([a-z]+)', 2),
(2, 'Hello World', '([A-Z][a-z]+) ([A-Z][a-z]+)', 0),
(3, '123-456-789', '([0-9]{3})-([0-9]{3})-([0-9]{3})', 3),
(4, 'example@example.com', '([a-z]+)@([a-z]+)\\.([a-z]+)', 1),
(5, '测试文本 test text', '(\\p{Han}+) (.+)', 1);
SELECT id, REGEXP_EXTRACT_OR_NULL(text_column, pattern_column, position_column) AS extracted_result
FROM test_regexp_extract_or_null
ORDER BY id;
+------+-----------------+
| id | extracted_result|
+------+-----------------+
| 1 | 123 |
| 2 | Hello World |
| 3 | 789 |
| 4 | example |
| 5 | 测试文本 |
+------+-----------------+
이모지 예제
SELECT regexp_extract_or_null('😀😊😎', '😀|😊|😎',0);
+------------------------------------------------------------+
| regexp_extract_or_null('😀😊😎', '😀|😊|😎',0) |
+------------------------------------------------------------+
| 😀 |
+------------------------------------------------------------+
'str'이 NULL이면 NULL을 반환합니다.
SELECT REGEXP_EXTRACT_OR_NULL(NULL, '([a-z]+)', 1);
+---------------------------------------------+
| REGEXP_EXTRACT_OR_NULL(NULL, '([a-z]+)', 1) |
+---------------------------------------------+
| NULL |
+---------------------------------------------+
'pattern'이 NULL이면 NULL을 반환합니다.
SELECT REGEXP_EXTRACT_OR_NULL('Hello World', NULL, 1);
+------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('Hello World', NULL, 1) |
+------------------------------------------------+
| NULL |
+------------------------------------------------+
'pos'가 NULL이면 NULL을 반환합니다.
SELECT REGEXP_EXTRACT_OR_NULL('Hello World', '([a-z]+)', NULL);
+---------------------------------------------------------+
| REGEXP_EXTRACT_OR_NULL('Hello World', '([a-z]+)', NULL) |
+---------------------------------------------------------+
| NULL |
+---------------------------------------------------------+
모든 매개변수가 NULL이면 NULL을 반환합니다.
SELECT REGEXP_EXTRACT_OR_NULL(NULL,NULL,NULL);
+----------------------------------------+
| REGEXP_EXTRACT_OR_NULL(NULL,NULL,NULL) |
+----------------------------------------+
| NULL |
+----------------------------------------+
pattern이 유효한 정규 표현식이 아니면 오류가 발생합니다.
mysql> SELECT REGEXP_EXTRACT_OR_NULL('123AbCdExCx', '([[:lower:]]+)C([[]ower:]]+)', 1);
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.2)[INVALID_ARGUMENT]Could not compile regexp pattern: ([[:lower:]]+)C([[:lower:]+)
Error: missing ]: [[:lower:]+)
고급 정규 표현식
SELECT regexp_extract_or_null('foo123bar', '(?<=foo)(\\d+)(?=bar)', 1);
-- ERROR 1105 (HY000): errCode = 2, detailMessage = (127.0.0.1)[INVALID_ARGUMENT]Invalid regex pattern: (?<=foo)(\d+)(?=bar). Error: invalid perl operator: (?<
SET enable_extended_regex = true;
SELECT regexp_extract_or_null('foo123bar', '(?<=foo)(\\d+)(?=bar)', 1);
+-----------------------------------------------------------------+
| regexp_extract_or_null('foo123bar', '(?<=foo)(\\d+)(?=bar)', 1) |
+-----------------------------------------------------------------+
| 123 |
+-----------------------------------------------------------------+
패턴 수정자
대소문자를 구분하지 않는 매칭: (?i)를 사용하면 매칭 시 대소문자를 구분하지 않습니다.
SELECT REGEXP_EXTRACT_OR_NULL('Hello World', '(hello)', 1) AS case_sensitive,
REGEXP_EXTRACT_OR_NULL('Hello World', '(?i)(hello)', 1) AS case_insensitive;
+----------------+------------------+
| case_sensitive | case_insensitive |
+----------------+------------------+
| NULL | Hello |
+----------------+------------------+
.은 기본적으로 줄 바꿈 문자와 일치합니다. (?-s)를 사용하면 .이 줄 바꿈 문자와 일치하지 않습니다.
SELECT REGEXP_EXTRACT_OR_NULL('foo\nbar', '^(.+)$', 1) AS dot_match_nl,
REGEXP_EXTRACT_OR_NULL('foo\nbar', '(?-s)^(.+)$', 1) AS dot_not_match_nl;
+--------------+------------------+
| dot_match_nl | dot_not_match_nl |
+--------------+------------------+
| foo
bar | NULL |
+--------------+------------------+
여러 줄 모드: (?m)을 사용하면 ^와 $가 각 줄의 시작과 끝에 일치합니다.
SELECT REGEXP_EXTRACT_OR_NULL('foo\nbar', '^(bar)', 1) AS single_line,
REGEXP_EXTRACT_OR_NULL('foo\nbar', '(?m)^(bar)', 1) AS multi_line;
+-------------+------------+
| single_line | multi_line |
+-------------+------------+
| NULL | bar |
+-------------+------------+
탐욕적 매칭과 비탐욕적 매칭: (?U)를 사용하면 수량자가 가능한 한 적게 매칭합니다.
SELECT REGEXP_EXTRACT_OR_NULL('aXbXc', '(a.*X)', 1) AS greedy,
REGEXP_EXTRACT_OR_NULL('aXbXc', '(?U)(a.*X)', 1) AS non_greedy;
+--------+------------+
| greedy | non_greedy |
+--------+------------+
| aXbX | aX |
+--------+------------+