정규식(Regular expression) 문자열 함수

정규식(Regular expression) 문자열 함수

이 문자열 함수들은 정규식(줄여서 "regex")과 일치하는 연산을 수행합니다.

출처: Snowflake SQL Reference

본문

정규식 함수 목록

함수	비고
[ NOT ] REGEXP	RLIKE의 별칭
REGEXP_COUNT
REGEXP_EXTRACT_ALL	REGEXP_SUBSTR_ALL의 별칭
REGEXP_INSTR
REGEXP_LIKE	RLIKE의 별칭
REGEXP_REPLACE
REGEXP_SUBSTR
REGEXP_SUBSTR_ALL
[ NOT ] RLIKE

일반 사용 참고 사항

이 참고 사항에서 "대상(subject)"은 연산 대상 문자열을, "패턴(pattern)"은 정규식을 가리킵니다.

• 대상은 보통 변수 컬럼이고 패턴은 보통 상수이지만, 반드시 그래야 하는 것은 아닙니다. 정규식 함수의 모든 인수는 상수 또는 변수가 될 수 있습니다.
• 패턴은 대부분의 POSIX ERE(확장 정규식) 구문을 지원합니다. 자세한 내용은 POSIX basic and extended 섹션(위키피디아)을 참고하세요. 한 가지 예외로, 정규식 함수는 *?, ??, +? 같은 비탐욕적(non-greedy) 수량자를 지원하지 않습니다.
• 패턴은 또한 다음 Perl 백슬래시 시퀀스를 지원합니다.
  • \d: 십진 숫자(0-9).
  • \D: 십진 숫자가 아님.
  • \s: 공백 문자.
  • \S: 공백 문자가 아님.
  • \w: "단어" 문자(a-z, A-Z, 밑줄("_"), 십진 숫자).
  • \W: 단어 문자가 아님.
  • \b: 단어 경계.
  • \B: 단어 경계가 아님.
• 자세한 내용은 Character classes(위키피디아) 또는 Backslash sequences(Perl 문서) 섹션을 참고하세요.

참고: single-quoted string constants(작은따옴표 문자열 상수)에서는 백슬래시 시퀀스의 백슬래시 문자를 이스케이프해야 합니다. 예를 들어 \d를 지정하려면 \\d를 사용하세요. 자세한 내용은 이 주제의 Single-quoted string constants에서 정규식 지정을 참고하세요. 문자열을 작은따옴표 대신 달러 기호 쌍($$)으로 구분하면 백슬래시를 이스케이프할 필요가 없습니다.

• 기본적으로 패턴의 POSIX 와일드카드 문자 .는 대상의 개행 문자 \n을 일치 대상에 포함하지 않습니다. 개행 문자도 일치시키려면 pattern 인수에서 .를 (.|\n)로 바꾸거나 parameters 인수에 s 파라미터를 사용하세요(아래 설명).
• 모든 정규식 함수는 유니코드를 지원합니다. 단일 유니코드 문자는 해당 문자의 이진 표현의 바이트 길이와 무관하게 항상 한 문자로 계산됩니다(즉, POSIX 메타 문자 .는 정확히 한 유니코드 문자와 일치). 또한 대상 오프셋을 받거나 반환하는 함수에서도 단일 유니코드 문자는 1로 계산됩니다.

정규식 파라미터 지정

대부분의 정규식 함수는 선택적 parameters 인수를 지원합니다. parameters 인수는 정규식 함수의 일치 동작을 지정하는 VARCHAR 문자열입니다. 지원되는 파라미터는 다음과 같습니다.

파라미터	설명
c	대소문자 구분 일치를 활성화합니다.
i	대소문자 무시 일치를 활성화합니다.
m	멀티라인 모드를 활성화합니다(즉, 메타 문자 ^와 $가 대상의 각 줄의 시작과 끝을 표시). 기본적으로 멀티라인 모드는 비활성화되어 있습니다(즉, ^와 $가 대상 전체의 시작과 끝을 표시).
e	부분 일치(submatch)를 추출합니다. REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_SUBSTR_ALL 및 이 함수들의 별칭에만 적용됩니다.
s	POSIX 와일드카드 문자 .가 \n과 일치하도록 활성화합니다. 기본적으로 와일드카드 문자 일치는 비활성화되어 있습니다.

기본 문자열은 c이며 다음을 지정합니다.

• 대소문자 구분 일치.
• 싱글라인 모드.
• 부분 일치 추출 없음(단, 항상 부분 일치 추출을 사용하는 REGEXP_REPLACE는 예외).
• POSIX 와일드카드 문자 .가 \n 개행 문자와 일치하지 않음.

여러 파라미터를 지정할 때는 공백이나 구분자 없이 문자열로 입력하세요. 예를 들어 ims는 POSIX 와일드카드 일치가 활성화된 멀티라인 모드에서 대소문자 무시 일치를 지정합니다.

parameters 문자열에 c와 i가 모두 포함되면, 문자열에서 마지막에 오는 것이 함수가 대소문자 구분 일치를 수행할지 대소문자 무시 일치를 수행할지를 결정합니다. 예를 들어 ci는 마지막에 i가 오므로 대소문자 무시 일치를 지정합니다.

다음 예제는 대소문자 구분 일치와 대소문자 무시 일치의 결과가 어떻게 달라질 수 있는지 보여줍니다. REGEXP_COUNT 함수는 대소문자 구분 일치(c 파라미터, 기본값)에서는 snow와 SNOW에 대해 0개를, 대소문자 무시 일치(i 파라미터)에서는 1개를 반환합니다:

SELECT REGEXP_COUNT('snow', 'SNOW', 1, 'c') AS case_sensitive_matching,
       REGEXP_COUNT('snow', 'SNOW', 1, 'i') AS case_insensitive_matching;

+-------------------------+---------------------------+
| CASE_SENSITIVE_MATCHING | CASE_INSENSITIVE_MATCHING |
|-------------------------+---------------------------|
|                       0 |                         1 |
+-------------------------+---------------------------+

REGEXP_SUBSTR 함수를 e 파라미터와 함께 사용해 단어 Release, 그 뒤의 하나 이상의 비단어 문자, 그 뒤의 하나 이상의 숫자를 찾고, 숫자와 일치하는 부분 문자열을 반환합니다:

SELECT REGEXP_SUBSTR('Release 24', 'Release\\W+(\\d+)', 1, 1, 'e') AS release_number;

+----------------+
| RELEASE_NUMBER |
|----------------|
| 24             |
+----------------+

파라미터를 사용하는 더 많은 예는 REGEXP_INSTR, REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_SUBSTR_ALL, [ NOT ] RLIKE를 참고하세요.

메타문자인 문자 일치

정규식에서 일부 문자는 특정 의미를 갖는 메타문자로 취급됩니다. 예를 들어:

• .는 임의의 단일 문자와 일치하는 메타문자입니다.
• *는 앞의 요소가 0회 이상 반복될 때 일치하는 수량자입니다. 예를 들어 BA*는 B, BA, BAA 등과 일치합니다.
• ?는 앞의 요소가 0회 또는 1회 있을 때 일치하는 수량자입니다.

실제 문자(예: 실제 마침표, 별표, 물음표)와 일치하려면 백슬래시로 메타문자를 이스케이프해야 합니다(예: \., \*, \? 등).

참고: single-quoted string constant에서 정규식을 사용한다면 두 번째 백슬래시로 백슬래시를 이스케이프해야 합니다(예: \\., \\*, \\? 등). 자세한 내용은 Single-quoted string constants에서 정규식 지정을 참고하세요.

예를 들어 문자열에서 여는 괄호(()를 찾아야 한다고 가정해 봅시다. 한 가지 방법은 백슬래시로 패턴의 문자를 이스케이프하는 것입니다(예: \().

패턴을 single-quoted string constant로 지정한다면 그 백슬래시도 두 번째 백슬래시로 이스케이프해야 합니다.

다음 패턴은 괄호 안에 나타나는 영숫자 문자의 시퀀스와 일치합니다(예: (NY)):

SELECT REGEXP_SUBSTR('Customers - (NY)','\\([[:alnum:]]+\\)') AS location;

+----------+
| LOCATION |
|----------|
| (NY)     |
+----------+

추가 예는 single-quoted string constant에서 메타문자 사용 예를 참고하세요.

dollar-quoted string constant를 사용한다면 백슬래시 문자를 이스케이프할 필요가 없습니다:

SELECT REGEXP_SUBSTR('Customers - (NY)',$$\\([[:alnum:]]+\\)$$) AS location;

+----------+
| LOCATION |
|----------|
| (NY)     |
+----------+

역참조(Backreferences) 사용

Snowflake는 정규식 패턴의 역참조(DDOUBLE_QUOTE_ESCAPE, 정식 언어 이론에서는 "squares"로 알려짐)를 지원하지 않지만, REGEXP_REPLACE 함수의 대체 문자열에서는 역참조를 지원합니다.

빈 패턴 지정

대부분의 regexp 함수에서 빈 패턴(즉, '')은 빈 대상조차 일치하지 않습니다.

예외는 REGEXP_LIKE와 그 별칭 [ NOT ] REGEXP, [ NOT ] RLIKE로, 이 함수들에서는 빈 패턴이 빈 대상과 일치합니다. 패턴이 양 끝에 암시적으로 고정(anchored)되기 때문입니다(즉, ''가 자동으로 '^$'가 됨).

빈 그룹(즉, 부분식 ())은 대상의 시작과 끝을 포함해 문자 사이의 공간과 일치합니다.

dollar-quoted string constant에서 정규식 지정

함수의 정규식을 지정하는 데 문자열 상수를 사용한다면 dollar-quoted string constant를 사용해 정규식에서 백슬래시 문자를 이스케이프하는 것을 피할 수 있습니다.(single-quoted string constants를 사용한다면 백슬래시를 이스케이프해야 합니다.)

dollar-quoted string constant의 내용은 항상 문자 그대로 해석됩니다.

예를 들어 메타문자를 이스케이프할 때는 백슬래시 하나만 사용하면 됩니다:

SELECT w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, $$\\?$$);

역참조를 사용할 때도 백슬래시 하나만 사용하면 됩니다:

SELECT w2, REGEXP_REPLACE(w2, '(.old)', $$very \\1$$)
  FROM wildcards
  ORDER BY w2;

single-quoted string constant에서 정규식 지정

single-quoted string constant에서 정규식을 사용한다면 백슬래시 시퀀스의 백슬래시를 두 번째 백슬래시로 이스케이프해야 합니다.

참고: 정규식에서 백슬래시 이스케이프를 피하려면 single-quoted string constant 대신 dollar-quoted string constant를 사용할 수 있습니다.

예를 들어:

• 백슬래시로 메타문자를 이스케이프한다면 그 백슬래시를 두 번째 백슬래시로 이스케이프해야 합니다. single-quoted string constant에서 메타문자 사용 예를 참고하세요.
• 백슬래시 시퀀스를 사용한다면 그 시퀀스의 백슬래시를 이스케이프해야 합니다.
• 역참조를 사용한다면 그 역참조의 백슬래시를 이스케이프해야 합니다. single-quoted string constant에서 역참조 사용 예를 참고하세요.

single-quoted string constant에서 메타문자 사용 예

이 예제는 물음표(?)를 검색하는 정규식의 이스케이프 시퀀스 일부로 백슬래시를 사용합니다.

한 컬럼에 백슬래시 하나, 다른 컬럼에 물음표 하나가 들어 있는 행을 가진 테이블을 만들고 행을 삽입합니다:

CREATE OR REPLACE TABLE wildcards (w VARCHAR, w2 VARCHAR);
INSERT INTO wildcards (w, w2) VALUES ('\\\\', '?');

다음 쿼리는 물음표 리터럴을 검색합니다. 검색은 정규식을 사용하는데, 정규식에서 물음표는 메타문자이므로 물음표를 리터럴로 취급하려면 이스케이프해야 합니다. 백슬래시가 문자열 리터럴에 나타나므로 백슬래시 자체도 이스케이프해야 합니다:

SELECT w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, '\\\\?');

+----+
| W2 |
|----|
| ?  |
+----+

다음 쿼리는 정규식이 두 문자(백슬래시 이스케이프 문자와 물음표)로 구성되어 있음을 더 쉽게 볼 수 있게 합니다:

SELECT w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, '\\\\' || '?');

+----+
| W2 |
|----|
| ?  |
+----+

앞의 예에서 추가 백슬래시는 이스케이프 문자가 문자열 리터럴의 일부였기 때문에만 필요했습니다. 정규식 자체에는 필요하지 않았습니다. 다음 SELECT 문은 SQL 명령 문자열의 일부로 문자열 리터럴을 파싱할 필요가 없으므로 문자열 리터럴이 필요로 했던 추가 이스케이프 문자가 필요하지 않습니다:

SELECT w, w2, w || w2 AS escape_sequence, w2
  FROM wildcards
  WHERE REGEXP_LIKE(w2, w || w2);

+---+----+-----------------+----+
| W | W2 | ESCAPE_SEQUENCE | W2 |
|---+----+-----------------+----|
| \ | ?  | \?              | ?  |
+---+----+-----------------+----+

single-quoted string constant에서 역참조 사용 예

문자열 리터럴에서 역참조(예: \1)를 사용한다면 그 역참조의 일부인 백슬래시를 이스케이프해야 합니다. 예를 들어 REGEXP_REPLACE의 대체 문자열 리터럴에서 역참조 \1을 지정하려면 \\1을 사용하세요.

다음 예제는 앞에서 만든 테이블을 사용합니다. SELECT는 역참조를 사용해 정규식 .old의 각 발생을 "very"라는 단어가 앞에 붙은 일치 문자열의 복사본으로 대체합니다:

INSERT INTO wildcards (w, w2) VALUES (NULL, 'When I am cold, I am bold.');

SELECT w2, REGEXP_REPLACE(w2, '(.old)', 'very \\\\1')
  FROM wildcards
  ORDER BY w2;

+----------------------------+------------------------------------------+
| W2                         | REGEXP_REPLACE(W2, '(.OLD)', 'VERY \\1') |
|----------------------------+------------------------------------------|
| ?                          | ?                                        |
| When I am cold, I am bold. | When I am very cold, I am very bold.     |
+----------------------------+------------------------------------------+

더 알아보기 (Learn more)

• String & binary functions (문자열·이진 함수)
• 문자열 데이터 타입 사용
• REGEXP_COUNT, REGEXP_SUBSTR 등 개별 함수 문서