Redshift : REGEXP_SUBSTR() (정규표현식 추출, 패턴 문자 추출, Regular Expression Substring)

 

 

 

Redshift에서 REGEXP_SUBSTR() 함수는 특정한 패턴을 주고 이 패턴에 일치하는 문자열을 return합니다.

 

 

Syntax

regexp_substr(string, pattern, start_position, occurence, search_parameter)

 

- string

pattern 검색을 할 대상이 될 string 또는 column 이름 입니다.

 

- pattern

검색할 pattern입니다.

 

- start_position

string에서 몇 번째 문자부터 검색을 시작할지를 나타냅니다.

1을 전달할 경우 string의 가장 첫 번째 문자부터 pattern 검색을 시작합니다.

(1이 default 값입니다.)

 

- occurence

string에서 pattern과 맞는 부분이 여러 개 발견될 경우 몇 번째로 일치하는 string을 return할지를 결정합니다.

default 값은 1이며 string에 있는 문자 수 보다 클 경우 null값이 return됩니다.

 

- search_parameter

pattern 매칭을 할 때 자세한 옵션들을 명시하는 부분입니다.

가장 많이 쓰일만한 것은 아래 2개와 같습니다.

c = case-sensitive하게 pattern 매칭을 합니다. 기본값은 대소문자를 구분하여(대소문자를 다른 문자로 간주하여 pattern 매칭을 합니다.)

i = case-insensitive하게 pattern 매칭을 합니다. 대소문자 구분을 하지 않습니다.

 

search_parameter는 string의 형태로 'c' 또는 'ce'와 같이 원하는 옵션을 순서대로 모두 명시해주면 됩니다.

(e는 다른 기능을 가진 옵션인데 여기서는 설명 생략하겠습니다. 관련 내용은 공식 문서에서 확인 가능합니다.)

https://docs.aws.amazon.com/redshift/latest/dg/REGEXP_SUBSTR.html

REGEXP_SUBSTR function - Amazon Redshift

 

 

 

사용 가능한 Pattern은 아래 표를 참고하면 됩니다.

(참고 : docs.aws.amazon.com/ko_kr/redshift/latest/dg/pattern-matching-conditions-posix.html)

Sign	Description
.	모든 문자를 일치시킵니다.
*	0개 이상의 발생 패턴을 일치시킵니다.
+	1개 이상의 발생 패턴을 일치시킵니다.
?	0개 또는 1개의 발생 패턴을 일치시킵니다.
|	대체할 일치 패턴을 지정합니다. 예를 들어 E | H 는 E 또는 H를 의미합니다.
^	라인 시작 문자를 일치시킵니다.
$	라인 끝 문자를 일치시킵니다.
$	문자열 끝을 일치시킵니다.
[ ]	대괄호는 일치하는, 즉 목록의 표현식 1개와 일치해야 하는 목록을 지정합니다. 일치하지 않는, 즉 목록의 표현식을 제외한 모든 문자와 일치하는 목록 앞에는(^)이 입력됩니다.
( )	그룹 항목을 괄호로 묶어 단일 논리 항목으로 처리합니다.
{m}	이전 항목을 정확히 m번 반복합니다.
{m,}	이전 항목을 m번 이상 반복합니다.
{m,n}	이전 항목을 m번 이상, n번 미만 반복합니다.
[: :]	POSIX 문자 클래스 안에 있는 모든 문자를 일치시킵니다. 다음 문자 클래스에서 Amazon Redshift는 ASCII 문자만 지원합니다. [:alnum:], [:alpha:], [:lower:],[:upper:]

 

 

 

Operator	Description	Same operator
\\d	숫자 문자	[[:digit:]]
\\D	숫자를 제외한 문자	[^[:digit:]]
\\w	단어 문자	[[:word:]]
\\W	단어를 제외한 문자	[^[:word:]]
\\s	공백 문자	[[:space:]]
\\S	공백을 제외한 문자	[^[:space:]]

 

 

 

 

 

 

 

 

 

 

Table name = item_list

item_id	item_name
1	Apple 12kg, 1box
2	Grape 2KG, 1box
3	Peach 900g, 1box
4	파인애플 2kg, 2박스
5	시금치 20g
6	수박 1.9kg, 2개
7	청포도 2.5KG, 1box

 

위같은 item_list라는 table이 있다고 가정해봅시다.

우리는 각 상품별로 item_name column에 있는 중량을 추출할 것입니다.

 

 

 

예시를 보면서 단계적으로 어떠한 pattern을 사용할 수 있고 어떤 pattern식을 이용해야 완벽하게 중량을 추출할 수 있는지를 봅시다.

 

일단 아래와 같은 query를 구성했습니다.

select  tl.item_id
        , tl.item_name

        , regexp_substr(tl.item_name, '[0-9]*kg', 1, 1, 'i') as weight
from item_list as tl
;

 

간단하게 0~9 사이의 숫자가 0번 이상 반복(*)되고 그 뒤에 kg라는 문자가 붙어있는 pattern을 찾으라는 것입니다.

i라는 parameter가 전달되었으니 대소문자를 구분하지 않고 매칭을 합니다.

 

item_id	item_name	weight
1	Apple 12kg, 1box	12kg
2	Grape 2KG, 1box	2KG
3	Peach 900g, 1box
4	파인애플 2kg, 2박스	2kg
5	시금치 20g
6	수박 1.9kg, 2개	9kg
7	청포도 2.5KG, 1box	5KG

 

그 결과는 위와 같습니다.

kg 앞에 숫자가 붙은 것들이 추출되었습니다.

 

다만 문제가 있죠.

 

첫 번째 문제는 g입니다.

중량 표현에는 kg 뿐 아니라 g도 있기에 g으로 표시된 중량은 추출해내지 못헀습니다.

 

두 번째 문제는 소수점 중량입니다.

pattern은 kg이라는 문자 왼쪽에 0~9 사이의 숫자가 0번 이상 반복되는 pattern을 찾는 것입니다.

따라서 1.9kg처럼 명시된 것은 9kg으로 추출됩니다.

 

 

 

이 문제를 하나씩 해결해봅시다.

 

select  tl.item_id
        , tl.item_name

        , regexp_substr(tl.item_name, '[0-9]*kg|[0-9]*g', 1, 1, 'i') as weight
from item_list as tl
;

 

일단 첫 번째 문제를 해결하기 위해 | 기호를 사용했습니다.

| 기호는 or라는 뜻을 가지고 있습니다.

즉, pattern을 전달할 때 단 한가지의 pattern이 아니라 여러 가지의 pattern을 전달해서

전달된 여러 가지의 pattern 중 하나라도 매칭이 되면 return을 하게 되는 것이죠.

 

[0-9]*kg|[0-9]*g

이것의 의미는

[0-9]*kg 라는 pattern과 일치하거나

[0-9]*g 라는 pattern과 일치하는

string을 return하라는 의미입니다.

 

 

 

select  tl.item_id
        , tl.item_name

        , regexp_substr(tl.item_name, '[0-9]*\\.*[0-9]*kg|[0-9]*\\.*[0-9]*g', 1, 1, 'i') as weight
from item_list as tl
;

 

두 번째 문제를 해결하기 위해 \\.를 사용했습니다.

Redshift에서의 pattern 기호 중에서는 .도 있습니다.

. 기호는 모든 문자를 의미하죠.

근데 저희는 . 그 자체를 명시하고 싶기 때문에 escape(\\)를 이용해서 .을 패턴 기호가 아닌 . 그 자체로 인식하라는 의미입니다.

 

그래서

[0-9]*\\.*[0-9]*kg

이 pattern의 의미는 다음과 같습니다.

0~9 숫자가 0번 이상 반복되고 + .이 0개 이상 있고 + 0~9 숫자가 0번 이상 반복되고 + kg라는 텍스트가 있는 pattern입니다.

 

가령

1.2kg

123.456kg

10.0kg

10kg

50kg

8kg

이런 pattern이 되겠죠.

 

그래서 이 query를 돌리면 아래와 같은 결과가 나올겁니다.

 

item_id	item_name	weight
1	Apple 12kg, 1box	12kg
2	Grape 2KG, 1box	2KG
3	Peach 900g, 1box	900g
4	파인애플 2kg, 2박스	2kg
5	시금치 20g	20g
6	수박 1.9kg, 2개	1.9kg
7	청포도 2.5KG, 1box	2.5KG

 

이제 모든 중량이 제대로 추출된 것을 볼 수 있습니다.