Redshift : SIMILAR TO (형태 비교)

 

 

SIMILAR TO

SIMILAR TO는 어떤 값을 특정 패턴과 비교하여 Boolean(True, False) 값을 반환합니다.
다만 다른 언어와는 다르게 SIMILAR TO의 비교는 글자 전체가 같아야 True가 반환된다는 특징이 있습니다.
글자의 일부만 동일하다면 이것은 동일하지 않다고 판단될 것입니다.
만약 어떤 피연산자가 NULL값이라면 반환되는 값도 NULL일 것입니다.

 

 

 

Characters들의 비교

보통 아래와 같은 특수 기능 문자들을 제외한 나머지 문자들은 별도의 기능 없이 문자 그대로의 의미를 갖게 됩니다.

[ ] ( ) | ^ - + * % _ ? { /

 

/는 escpae caracter로서 위에 나온 특수 기능 문자들의 기능을 없애고 그 문자 자체를 사용할 수 있도록 해주는 문자입니다.

e.g.
LIKE 'ab_c' -> _는 어떤 한 글자를 의미하는 Wildcard로 적용됨.
LIKE 'ab/_c' -> ab_c라는 문자 자체를 의미

위와 같은 특수 기능 문자들이 존재하지 않는 일반적인 값에 대해서 SIMILAR TO를 사용하면 비교 대상들이 완전히 동일해야만 True값을 반환합니다.
마치 등호(=)와 같은 의미인 것 처럼요.

SELECT 'Apple' SIMILAR TO 'Apple'; -- True 반환
SELECT 'Apples' SIMILAR TO 'Apple'; -- False 반환
SELECT 'Apple' SIMILAR TO 'Apples'; -- False 반환
SELECT 'APPLE' SIMILAR TO 'Apple'; -- SQL 서버 정책에 따라 다름

 

 

 

 

Wildcards

SIMILAR TO 함수는 SQL에서 사용할 수 있는 wildcard인 _와 %를 사용할 수 있습니다.
_는 한 글자를 의미하며 %는 0개 이상의 아무 글자를 의미합니다.

SELECT 'Apple' SIMILAR TO 'A_ple'; -- True 반환
SELECT 'Apple' SIMILAR TO 'A_le'; -- False 반환
SELECT 'Apple' SIMILAR TO 'A%le'; -- True 반환
SELECT 'Apple' SIMILAR TO 'App%le%'; -- True 반환
SELECT 'Apple' SIMILAR TO 'Appp%le%'; -- False 반환

%는 0개 이상의 텍스트를 의미합니다.

 

 

 

 

 

Character classes

대괄호로 묶인 글자들은 character class라고 불립니다.
이것은 SIMILAR TO 함수를 이용해 텍스트 비교를 할 때 어떤 글자가 Character class안에 속한 글자면 True를 반환하고 그렇지 않다면 False를 반환합니다.

 

SELECT 'Apple' SIMILAR TO 'A[abp]ple'; -- True 반환
SELECT 'Apple' SIMILAR TO 'Ap[pl]e'; -- False 반환
SELECT 'Apple' SIMILAR TO 'A[pl][pl]le'; -- True 반환

 

두 번째 예시에서 알 수 있듯이 대괄호 안에 묶여있는 여러 개의 텍스트는 단 한개씩만 적용되어 비교를 진행합니다.
두 번째 예시에서 Character class로 p와 l이 속해있고 이 두 글자를 모두 포함하면 Apple이라는 글자가 완성되어 True가 반환될테지만 사실은 그렇지 않습니다.
p와 l 중에서 하나만 적용시켜 글자를 비교하기 때문에 False가 반환됩니다.

이를 해결하려면 세 번째 예시에서처럼 두 개의 Chracter class를 사용하는 방법이 있습니다.

 

SELECT 'Apple' SIMILAR TO 'A[k-q]ple'; -- True 반환
SELECT 'Apple' SIMILAR TO 'Ap[abm-rvw]e'; -- True 반환
SELECT 'Apple' SIMILAR TO 'A[bcf-hj-mqtw]le'; -- False 반환

 

하이픈(-)으로 어떤 두 글자를 연결하여 Character class에 범위의 형태로 명시해주게 되면 그 글자들 사이에 존재하는 모든 글자가 유효한 비교 대상으로 간주됩니다.
첫 번째 예시의 경우 [klmnopq]와 동일한 의미를 가집니다. 즉, 경계에 명시된 k, q와 그 사이에 있는 모든 글자들이 비교 대상에 포함됩니다.

두 번째, 세 번째 예시에서처럼 하이픈으로 연결한 텍스트 Range는 별도의 구분자 없이 Character class 내의 어떤 곳에서건 사용될 수 있습니다.

다음에 나오는 내장 Character class(Predefined Character Class)는 정해진 의미를 가진 Character Class이며 SIMILAR TO 함수에 사용될 수 있습니다.

[:ALPHA:]
  a ~ z, A ~ Z의 라틴 문자를 나타냅니다. Accent-insensitive(엑센트 무시)합니다.

[:DIGIT:]
  0 ~ 9의 10진수 정수를 의미합니다.

[:ALNUM:]
  [:ALPHA:]와 [:DIGIT:] 두 Character Class를 합친 Class입니다.

[:UPPER:]
  A ~ Z의 대문자 라틴 문자를 나타냅니다. Accent-insensitive(엑센트 무시)합니다.

[:LOWER:]
  a ~ z의 소문자 라틴 문자를 나타냅니다. Accent-insensitive(엑센트 무시)합니다.

[:SPACE:]
  ANCII 32의 공백 글자를 의미합니다.

[:WHITESPACE:]
  Vertical tab(ASCII 9), Linefeed(ASCII 10), horizontal tab(ASCII 11), formfeed(ASCII 12), carriage return(ASCII 13), space(ASCII 32)를 의미합니다.

 

SELECT 'Apple' SIMILAR TO 'A[[:ALNUM:]]ple'; -- True 반환
SELECT 'Apple' SIMILAR TO 'A[[:DIGIT:]]ple'; -- False 반환
SELECT 'Apple' SIMILAR TO 'A[a[:SPACE:]b]ple'; -- False 반환
SELECT 'Apple' SIMILAR TO '[[:ALPHA:]]'; -- False 반환
SELECT 'A' SIMILAR TO '[[:ALPHA:]]'; -- True 반환

위 예시처럼 Predefined Character Class를 사용하면 마치 해당 Class의 element를 Character Class 안에 모두 적어준 것과 동일한 효과를 가집니다.

SELECT 'Apple' SIMILAR TO 'Appl[^eh-o]'; -- False 반환
SELECT 'Apple' SIMILAR TO 'A[^p][^p]le'; -- False 반환
SELECT 'Apple' SIMILAR TO 'A[^[:DIGIT:]]ple'; -- True 반환

 

위 예시처럼 Caret 기호(^)는 Caret기호 뒤에 명시된 모든 글자를 제외한 나머지 글자를 비교 대상에 포함하도록 합니다.
[^eh-o]는 e, h, i, j, k, l, m, n, o를 제외한 모든 글자를 의미합니다.

SELECT 'Apple' SIMILAR TO 'Appl[a-f^h-o]'; -- True 반환
SELECT 'Apple' SIMILAR TO 'A[gk^xyz]ple'; -- True 반환
SELECT 'Apple' SIMILAR TO 'A[abcd^hpy]ple'; -- False 반환
SELECT 'Apple' SIMILAR TO 'A[bp^hpy]ple'; -- False 반환

SELECT '3' SIMILAR TO '[[:DIGIT:]^4-8]'; -- True 반환
SELECT '6' SIMILAR TO '[[:DIGIT:]^4-8]'; -- False 반환

 

 

Caret 기호가 맨 처음에 명시되어있지 않다면 Caret 기호 뒤에 명시된 글자는 비교 대상에서 제외되며 Caret 기호보다 앞쪽에 명시된 글자는 비교 대상에 포함됩니다.
네 번째 예시에서처럼 비록 Caret 기호 앞에 b와 p가 명시되어 있지만 Caret기호 뒤에 h, p, y가 적혀져 있으므로 p는 비교대상에서 제외됩니다.

 

 

 

Quantifiers

SELECT 'Hallon' SIMILAR TO 'Hal?on' -- False 반환
SELECT 'Hallon' SIMILAR TO 'Hal?lon' -- True 반환
SELECT 'Hallon' SIMILAR TO 'Halll?on' -- True 반환
SELECT 'Hallon' SIMILAR TO 'Hallll?on' -- False 반환
SELECT 'Hallon' SIMILAR TO 'Halx?lon' -- True 반환
SELECT 'Hallon' SIMILAR TO 'H[a-c]?llon[x-z]?' -- True 반환

 

어떤 글자 뒤에 Question mark를 쓰면 Question mark 앞에 있는 문자가 0개 또는 1개 있는걸로 간주됩니다.
Hal?on의 경우 Haon 또는 Halon을 의미합니다.
Halll?on의 경우는 Hallon 또는 Halllon을 의미합니다.

 

SELECT 'Icaque' SIMILAR TO 'Ica*que' -- True 반환
SELECT 'Icaque' SIMILAR TO 'Icar*que' -- True 반환
SELECT 'Icaque' SIMILAR TO 'I[a-c]*que' -- True 반환
SELECT 'Icaque' SIMILAR TO '_*' -- True 반환
SELECT 'Icaque' SIMILAR TO '[[:ALPHA:]]*' -- True 반환
SELECT 'Icaque' SIMILAR TO 'Ica[xyz]*e' -- False 반환

어떤 글자 뒤에 *를 쓰면 해당 글자가 0개 이상 존재하는 것으로 간주됩니다.
Ica*que의 경우 Icque, Icaque, Icaaque, Icaaaque, ...등을 의미합니다.
_*의 경우 _는 어떤 임의의 텍스트를 의미하고 *는 이것이 0번 이상 반복되어 존재함을 의미하므로 이것은 거의 조합가 가능한 모든 글자를 나타냅니다.

 

 

SELECT 'Jujube' similar to 'Ju_+' -- True 반환
SELECT 'Jujube' similar to 'Ju+jube' -- True 반환
SELECT 'Jujube' similar to 'Jujuber+' -- False 반환
SELECT 'Jujube' similar to 'J[jux]+be' -- True 반환
SELECT 'Jujube' sililar to 'J[[:DIGIT:]]+ujube' -- False 반환

Plus sign이 어떤 글자 뒤에 명시되면 해당 글자가 1개 이상 존재하는 것으로 간주됩니다.

 

 

SELECT 'Kiwi' similar to 'Ki{2}wi' -- False 반환
SELECT 'Kiwi' similar to 'K[ipw]{2}i' -- True 반환
SELECT 'Kiwi' similar to 'K[ipw]{2}' -- False 반환
SELECT 'Kiwi' similar to 'K[ipw]{3}' -- True 반환

어떤 글자 뒤에 중괄호로 둘러싼 숫자를 적으면 해당 글자가 명시된 숫자만큼 반복됨을 의미합니다.
Ki{2}wi는 Kiiwi를 의미합니다.

 

 

SELECT 'Limone' similar to 'Li{2,}mone' -- False 반환
SELECT 'Limone' similar to 'Li{1,}mone' -- True 반환
SELECT 'Limone' similar to 'Li[nezom]{2,}' -- True 반환

어떤 글자 뒤에 중괄호로 둘러싼 숫자를 적고 그 숫자 뒤에 콤마를 추가하면 해당 글자가 최소 명시된 숫자 이상만큼 반복됨을 의미합니다.
Li{1,}mone은 Limone, Liimone, Liiimone, ...등을 의미합니다.

 

 

SELECT 'Mandarijn' similar to 'M[a-p]{2,5}rijn' -- True 반환
SELECT 'Mandarijn' similar to 'M[a-p]{2,3}rijn' -- False 반환
SELECT 'Mandarijn' similar to 'M[a-p]{2,3}arijn' -- True 반환

어떤 글자 뒤에 중괄호로 둘러싼 숫자 두 개를 적고 그 숫자를 콤마로 구분하면 그리고 두 번째 숫자가 첫 번째 숫자보다 크다면 해당 글자가 명시된 숫자 범위 만큼 반복됨을 의미합니다.
M[a-p]{2,3}arijn은 M[a-p][a-p]arijn 또는 M[a-p][a-p][a-p]arijn을 의미합니다.

따라서 ?, *, +는 {0, 1}, {0,}, {1,}와 동일한 의미입니다.

 

 

 

 

 

 

 

 

OR-ing terms

지금까지 봤던 Regular expression들은 또는(OR)을 의미하는 | 연산자와 같이 사용될 수 있습니다.

SELECT 'Nektarin' similar to 'Nek|tarin' -- False 반환
SELECT 'Nektarin' similar to 'Nektarin|Persika' -- True 반환
SELECT 'Nektarin' similar to 'M_+|N_+|P_+' -- True 반환

 

 

 

 

 

 

Subexpressions

Regular expression은 여러 개가 괄호 안에 묶여서 그룹화될 수 있으며 이를 Subexpression 또는 Subpattern이라고 부릅니다.
Subexpression은 모든 Regular expression과 Quantifier를 포함할 수 있습니다.

 

SELECT 'Orange' similar to 'O(ra|ri|ro)nge' -- True 반환
SELECT 'Orange' similar to 'O(r[a-e])+nge' -- True 반환
SELECT 'Orange' similar to 'O(ra){2,4}nge' -- False 반환
SELECT 'Orange' similar to 'O(r(an|in)g|rong)?e' -- True 반환

O(ra|ri|ro)nge는 Orange, Oringe, Oronge를 의미합니다.
O(r(an|in)g|rong)?e는 Oe, Orange, Oringe, Oronge를 의미합니다.

 

 

 

 

 

 

 

Escaping special characters

만약 비교하려는 원본 텍스트에 [, ], (, ), ?, * 등의 특정 기능을 가진 문자가 포함되어있으면 어떻게 비교를 할 수 있을까요?
이런 경우에는 저러한 문자들이 escape 되어야 합니다.
escape은 위와 같은 특수 기능 문자들 앞에 escape을 위한 문자를 적어 해당 특수 기능 문자가 기능을 띄지 않고 문자 그 자체의 의미만 지니게 해줘야합니다.

SELECT 'Peer (Poire)' similar to 'P[^ ]+ \(P[^ ]+\)' escape '\' -- True 반환
SELECT 'Pera [Pear]' similar to 'P[^ ]+ #[P[^ ]+#]' escape '#' -- True 반환
SELECT 'Päron-Äppledryck' similar to 'P%$-Ä%' escape '$' -- True 반환
SELECT 'Pärondryck' similar to 'P%--Ä%' escape '-' -- False 반환

한 가지 주의할 점은 SQL에선 어떤 escape text가 존재하지 않습니다.
따라서 escape '\', escape '#' 처럼 어떤 문자를 escape을 위한 text로 사용할지를 명시해주어야 합니다.

첫 번째 예시는 \를 escape 문자로 사용했으며
두 번째 예시는 #을, 세 번째 예시는 $을, 네 번째 예시는 -을 escape 문자로 사용했습니다.

첫 번째 예시에서 \(, \)가 있는데 \를 escape문자로 사용한다고 명시하였으므로 (와 )는 Subexpression 기능은 가지지 않고 그냥 괄호 문자 그 자체를 의미하게 됩니다.

또한 네 번째 예시에서는 escape text도 -이고 escape할 대상 text도 -입니다. 즉, escape text는 자기 자신도 escape 시킬 수 있습니다.

ref: https://firebirdsql.org/refdocs/langrefupd25-similar-to.html#langrefupd25-similar-to-building-regexps