[PostgreSQL, Greenplum] 문자열 패턴 매칭 (Pattern Matching)
Greenplum and PostgreSQL Database 2020. 7. 12. 23:13지난번 포스팅에서는 PostgreSQL, Greenplum DB에서 문자열형의 종류, 생성, 처리 연산자(Character Type Operators) 및 함수(Character Functions)에 대해서 소개(https://rfriend.tistory.com/543)하였습니다.
이번 포스팅에서는 PostgreSQL, Greenplum DB에서 문자열 패턴 매칭(String Pattern Matching)하는 세가지 방법을 소개하겠습니다.
(1) 전통적인 SQL LIKE 연산자 (LIKE Operator)
(2) SIMILAR TO 연산자 (the SIMILAR TO Operator)
(3) 정규 표현식 (POSIX-Style Regular Expressions)
(1) SQL LIKE, ~~ 연산자 ('LIKE', or '~~' Operator) |
'LIKE' 연산자는 전통적인 SQL에서 문자열 패턴 매칭할 때 가장 보편적으로 사용되며, 아래와 같은 syntax를 사용합니다.
string LIKE pattern [ESCAPE escape-character]
위의 LIKE 연산자는 LIKE 앞의 문자열이 LIKE 뒤의 패턴과 일치할 경우 TRUE를 반환하며, 일치하지 않을 경우는 FALSE를 반환합니다.
와일드카드 문자(wildcard character)로서 (1) 퍼센트 부호(%)는 그 위치부터 어떤 문자열(any string)이 와도 상관없으며, (2) 밑줄 부호(_)는 해당 위치에 하나의 문자가 일치하는지를 봐서 TRUE나 FALSE를 반환합니다. LIKE와 '%', '_' 와일드카드 문자를 사용한 패턴 매칭 예 몇개를 살펴보겠습니다.
pattern matching |
returns |
SELECT 'abc' LIKE 'abc'; |
true |
SELECT 'abc' LIKE 'a%'; |
true |
| SELECT 'abc' LIKE '%b'; | false |
SELECT 'abc' LIKE '%b%'; | true |
| SELECT 'abc' LIKE '%c'; | true |
SELECT 'abc' LIKE '_b_'; |
true |
| SELECT 'abc' LIKE 'c'; | false |
SELECT 'abc' LIKE '_c'; | false |
SELECT 'abc' LIKE '__c'; |
true |
'~~' 연산자를 'LIKE' 연산자 대신 사용할 수 있습니다. 위에 예 중에서 한개만 'LIKE'를 '~~' 연산자로 바꾸어보면 아래와 같습니다. (Jupyter Notebook에서 sql_magic 라이브러리로 SQL문 사용할 때 LIKE 대신 ~~ 연산자를 사용했었습니다.)
SELECT 'abc' ~~ 'a%'; --> returns true
|
패턴 일치여부를 판단하는데 있어 대소문자를 구분합니다.
SELECT 'abc' LIKE 'a%'; --> returns true SELECT 'abc' LIKE 'A%'; --> returns false
|
일치하지 않는 패턴을 찾고 싶으면 'NOT LIKE' 또는 '!~~' 연산자를 사용합니다.
SELECT 'abc' LIKE 'a%'; --> returns true SELECT 'abc' NOT LIKE 'a%'; --> retruns false SELECT 'abc' ~~ 'a%'; --> returns true SELECT 'abc' !~~ 'a%'; --> returns false
|
(2) SIMILAR TO 연산자 (the SIMILAR TO Operator) |
SELECT 'abc' SIMILAR TO 'abc'; -- true SELECT 'abc' SIMILAR TO 'a'; -- false SELECT 'abc' SIMILAR TO 'a__'; -- true SELECT 'abc' SIMILAR TO '_b_'; -- true SELECT 'abc' SIMILAR TO '_bc'; -- true SELECT 'abc' SIMILAR TO 'a%'; -- true SELECT 'abc' SIMILAR TO '%b'; -- false SELECT 'abc' SIMILAR TO '%b%'; -- true
|
-- | denotes alternation (either of two alternatives). -- Parentheses () can be used to group items into a single logical item. SELECT 'abc' SIMILAR TO '%(b|d)%'; -- true SELECT 'abc' SIMILAR TO '%(d|e)%'; -- false -- {m} denotes repetition of the previous item exactly m times. SELECT 'abbbc' SIMILAR TO '%b{3}%'; -- true SELECT 'abbbc' SIMILAR TO '%b{4}%'; -- false -- {m,} denotes repetition of the previous item m or more times. SELECT 'abbbc' SIMILAR TO '%b{2,}%'; -- true SELECT 'abbbc' SIMILAR TO '%b{4,}%'; -- false -- {m,n} denotes repetition of the previous item at least m and not more than n times. SELECT 'abbbc' SIMILAR TO '%b{2,3}%'; -- true
|
SELECT substring('foobar' from '%#"o_b#"%' for '#'); -- oob -- It returns null if there is no match. SELECT substring('foobar' from '#"o_b#"%' for '#'); -- NULL SELECT substring('foobar' from 'o...r'); -- oobar SELECT substring('foobar' from 'o(...)r'); -- oba
|
(3) 정규 표현식 (POSIX-Style Regular Expressions) |
정규표현식을 이용하면 위의 (1) LIKE 연산자, (2) SIMILAR TO 연산자보다 더욱 강력한 패턴 매칭을 할 수 있습니다.
-- Regular Expressions Matching Operators: ~, ~*, !~, !~* -- ~ operator: Matches regular expression, case sensitive SELECT 'thomas' ~ '.*thomas.*'; -- true SELECT 'thomas' ~ '.*Thomas.*'; -- False -- ~* operator: Matches regular expression, case insensitive SELECT 'thomas' ~* '.*Thomas.*'; -- true -- !~ operator: Does not match regular expression, case sensitive SELECT 'thomas' !~ '.*thomas.*'; -- False SELECT 'thomas' !~ '.*Thomas.*'; -- True -- !~* operator: Does not match regular expression, case insensitive SELECT 'thomas' !~* '.*Thomas.*'; -- false SELECT 'thomas' !~ '.*Thomas.*'; -- true SELECT 'thomas' !~* '.*steve.*'; -- true |
많은 도움이 되었기를 바랍니다.
이번 포스팅이 도움이 되었다면 아래의 '공감~
'를 꾹 눌러주세요. :-)
* Reference: https://www.postgresql.org/docs/9.5/functions-matching.html
