[SQL] SQL에서 정규표현식 활용하기

이번 포스팅에서는 정규표현식에 대한 개념과 SQL에서의 활용법에 대해 다룰 예정입니다.

---

0. 들어가며

SQL을 활용하다보면 where절에 문자열을 다루는 조건을 넣어 데이터를 추출하게 되는데요.

보통은 like 를 활용하거나 substr 등을 활용해서 데이터를 추출하게 됩니다.

그런데 조금 더 복잡한 규칙을 찾아내서 그에 해당하는 문자열을 뽑아올 때에는 and 조건이나 or조건을 이용하게 됩니다.

이렇게 되면 쿼리 작성을 할 때 귀찮게 반복을 하게 되고 따라서 쿼리문의 길이가 길어지죠.

 

이러한 부분을 어느정도 해결할 수 있는 방법이 바로 '정규표현식'을 활용하는 것입니다.

그러니 한번 정규표현식에 대해 한번 알아겠습니다.

---

 

1. 정규표현식이란?

정규표현식 (Regular Expression)
 - 특정한 규칙을 가진 문자열의 집합을 표현하는 데 사용하는 형식 언어

정규표현식은 문자열이 가지고 있는 규칙을 파악한 뒤 해당 규칙을 만족하는 문자열만 추출할 때 활용됩니다.

이에 대한 예시는 아래에서 사용 방법을 설명하며 살펴보기로 합시다.

 

정규표현식을 사용하면 얻을 수 있는 몇가지 장점이 있는데요.

1. SQL 뿐만아니라 Python, R 등 다양한 언어에서도 사용할 수 있다
2. 여러 정규표현식을 섞어서 사용한다면 정말 강력한 성능을 발휘한다.

여기서 문제는 정규표현식을 섞어서 사용하기가 어렵다는 것입니다...

 

그러니 이번 시간의 목표는 아래 2가지로 잡아봤습니다.

< 목표 >
1. 구성하고 있는 기본적인 메타 문자와 그 사용 방법에 대해서 알아보기 (기초를 튼튼히)
2. 보다 복잡한 정규표현식을 만들 땐 구글링을 해서 적용 가능한 실력 만들어두기

그럼 먼저 기본적인 정규표현식 사용 방법에 대해 알아봅시다.

 

---

2. 정규표현식의 패턴(Pattern)

아래의 예시들은 아래의 링크에서 확인할 수 있습니다.

RegexOne - Learn Regular Expressions - Lesson 1: An Introduction, and the ABCs

그럼 차근차근 살펴보도록 하죠.

 

2.1. 문자 매칭

2.1.1. 같은 문자 찾기

1) 문자 그대로 입력

우선 정규표현식을 활용하여 원하는 문자를 가지고 있는 문자열을 뽑을 땐 원하는 문자를 그대로 적어주면 됩니다.

출처 :&nbsp;https://regexone.com/lesson/introduction_abcs

출처 : https://regexper.com/

위와 같이 abc 라고 정규표현식에 입력을 해주면 'abc' 라는 문자열을 포함하고 있는 문자열을 뽑아줍니다.

구조도를 확인하면 조금 더 이해가 쉬울 것 같습니다.

 

2) 여러 패턴의 같은 문자 찾기

그리고 찾으려는 패턴을 여러개 입력하고 싶다면 | 를 이용하면 됩니다.

| 는 or 의 역할을 하여 여러 패턴 중 하나의 패턴이라도 만족시키는 문자열을 추출해줍니다. 

 

3) 같은 문자 찾기 정리

원하는 문자열 그대로를 추출 하려는 경우를 정리 해보면 아래와 같이 정리를 할 수 있습니다.

패턴	기능	사용법	설명
문자열 그대로 입력	작성한 패턴을 포함한 문자열 추출	"abc"	abc 를 포함한 문자열 추출
|	| 로 구분된 패턴 중 하나라도 해당하는 문자열을 추출 (or 역할)	"abc|dr"	abc 또는 dr 을 포함한 문자열 추출

그런데 우리가 문자열을 활용할 땐 정확히 어떤 문자를 가지지 않는 패턴인 경우가 많습니다.

그럴 경우에는 문자 그룹을 나타낼 수 있는 표현식을 활용해야합니다.

다음으로는 문자 그룹에 대해 살펴보겠습니다.

 

2.1.2. 문자 그룹

1) 문자 그룹 기본 원리 : [ ], -

문자 그룹을 나타내는 기본적인 패턴은 대괄호( [ ] )를 사용하는 것입니다.

이 대괄호 안에 어떤 문자들을 넣어주느냐에 따라 정규표현식이 찾아주는 문자열이 달라지는데요.

대괄호 안에서 대시(-)를 통해 문자의 범위를 나타낼 수 있습니다.

이해를 위해 예시를 살펴보죠.

 

2) 알파벳 소문자, 대문자, 숫자, 한글

정규표현식에서 알파벳을 나타낼 때, 소문자의 경우 [a-z], 대문자의 경우 [A-Z] 로 나타냅니다.

그리고 숫자의 경우 [0-9], 한글의 경우 [가-힣] 으로 표현합니다.

이 의미는 'a 부터 z 사이(대문자는 A부터 Z까지, 숫자는 0부터 9까지, 한글은 가 부터 힣 까지)에 있는 알파벳인 경우 추출해줘!' 라는 의미가 됩니다.

위 예시를 보면 [A-Z] 라는 패턴을 입력함으로써 맨 앞자리의 문자열이 대문자 알파벳인 경우를 찾아줬습니다.

만약 알파벳 전체가 아닌 특정 문자의 범위를 나타내고 싶다면 아래와 같이 나타낼 수 있습니다. 

 

3) 특정한 범위 나타내기 (연속)

먼저 예시를 살펴보시죠.

[a-c] 라는 패턴을 입력함으로써 'a ~ c 까지의 알파벳을 포함한다면 추출해줘!' 라는 의미가 되었습니다. 

마찬가지로 숫자의 경우 [0-5] 와 같이 활용할 수 있고, 한글의 경우에도 [가-찿] 와 같이 활용할 수 있습니다.

그리고 만약 연속된 알파벳이 아닌 경우에는 문자열을 나열해줌으로써 원하는 패턴을 만들 수 있습니다.

아래의 예시에서 살펴보시죠.

 

4) 특정한 범위 나타내기 (불연속)

연속된 알파벳이 아닐 땐, [] 안에 추출할 문자를 적어주면 됩니다.

이렇게 c, m, f 라는 세 알파벳을 포함하는 경우를 찾고싶다면 대괄호 안에 해당 문자들을 넣어주면 됩니다.

 

 

5) 문자 그룹 정리

문자 그룹에 대한 패턴을 간단히 정리하면 이렇습니다.

패턴	기능	예시	설명
[a-z]	알파벳 소문자를 포함한 문자열을 찾아줌	"[a-z]"	알파벳 소문자를 포함한 문자열을 찾음
[A-z]	알파벳 대분자 또는 소문자를 포함한 문자열을 찾아줌	"[A-z]"	알파벳 대분자 또는 소문자를 포함한 문자열을 찾음
[d-g]	알파벳 소문자 d ~ g 를 포함한 문자열을 찾아줌	"[d-g]"	알파벳 소문자 d ~ g 를 포함한 문자열을 찾음
[abdhs]	a, b, d, h, s 를 포함한 문자열을 찾아줌	[abdhs]	a, b, d, h, s 를 포함한 문자열을 찾음

위 내용 이외에 [ ] 을 활용한 문자 그룹에서 착각하기 쉬운 내용을 정리하고 갈 필요가 있습니다.

바로 [ ] 를 활용하면 문자 하나에 대한 패턴이 만들어진다는 것인데요.

abc 라는 패턴을 입력한다면 “abc”라는 패턴을 포함한 문자열을 뽑아주지만, [abc]는 a 또는 b 또는 c 를 포함한 문자열을 찾아줍니다.

만약 하나가 아닌 여러 문자에 대해 패턴을 적용하고 싶다면 다른 정규표현식 패턴을 함께 사용해주어야 합니다.

 

 

2.2. 패턴 반복 or 제한하기

문자 그룹([ ])을 활용할 때 여러 문자에 대해 패턴을 적용할 때 사용하는 정규표현식은 아래와 같습니다.

 

1) 0회 이상 포함 : *

패턴을 입력한 뒤, *를 붙혀주면 그 패턴을 0회 이상 포함하는 문자열을 추출해줍니다.

아래의 구조도는 "[a-c]*" 라고 입력했을 때의 구조입니다.

0회 이상을 포함하는 문자열을 추출하기 때문에 해당 패턴에 해당하는 문자가 없어도 문자열이 추출됩니다.

즉, 모든 문자열을 가져온다는 느낌인거죠.

 

2) 1회 이상 포함 : +

패턴을 입력한 뒤, +를 붙혀주면 그 패턴을 1회 이상 포함하는 문자열을 추출해줍니다.

아래의 구조도는 "[a-c]+" 라고 입력했을 때의 구조입니다.

 붙혀주 * 와 다른 점은 해당 패턴이 적어도 1회는 반복되어야 한다는 점입니다.

 

3) n회 이상, m회 이하 포함 : {n,m}

조금 더 디테일하게 반복을 설정해줄 땐 {} 를 활용할 수 있습니다.

아래의 구조도는 "[a-c]{2,4}" 라고 입력했을 때의 구조입니다.

a~c 가 한 번 등장한 뒤, 그 후로 1~3번 더 반복되는 경우를 추출해준다는 의미입니다.

즉, a~c의 문자가 2~4번 반복되는 문자열을 추출해준다는 것이죠.

 

4) 0회 or 1회 포함 : ?

마지막으로 ? 를 활용하면 해당 패턴이 0번 또는 1번 포함되는 경우를 추출할 수 있습니다.

* 와 다른 점은 해당 패턴이 2번 이상 반복되는 경우엔 추출이 되지 않는다는 점이 있습니다.

아래의 구조도는 "[a-c]?" 라고 입력했을 때의 구조입니다.

 

5) 패턴의 반복 정리

위의 내용들을 정리하면 아래와 같이 정리할 수 있습니다.

패턴	기능	예시	설명
*	0회 이상 패턴 반복된 문자열을 찾아줌	[0-4]*	0 ~ 4에 해당하는 숫자가 0회 이상 반복되는 문자열을 추출
+	1회 이상 패턴 반복된 문자열을 찾아줌	[가-힣]+	한글이 1회 이상 반복되는 문자열을 추출
{n,m}	n회 이상, m회 이하로 패턴이 반복된 문자열을 찾아줌	[abc]{3,5}	a 또는 b 또는 c 가 3번 이상, 5번 이하 반복되는 문자열을 추출 "aacd", "accd", "abc" 추출 / "adf", "ab" 추출X
?	0회 또는 1회 패턴 반복된 문자열을 찾아줌	[안녕]?	'안' 또는 '녕' 문자가 0~1회 반복되는 경우 추출 "안대", "기쁨", "선물" 추출 / "안녕", "안과안경" 추출 X

 

2.2. 패턴 위치 제한 걸어주기

다음으로는 패턴의 위치를 제한하는 방법에 대해 알아봅시다.

 

1) 맨 앞 위치로 제한하기 : ^

정규표현식으로 추출할 패턴에 해당하는 문자가 문자열 제일 앞에 있는 경우만 추출할 땐 ^ 를 사용합니다.

이렇게 Mission 이라는 문자를 포함하고 있지만 문자열의 시작이 Mission 인 경우만 추출하였습니다.

 

 

2) 맨 마지막 위치로 제한하기 : $

반대로 가장 마지막에 추출하고자 하는 패턴이 들어가는 경우엔 $ 를 사용합니다.

문자열 마지막에 successful 이라는 문자가 들어있는 문자열을 추출했습니다.

 

3) 자리 하나를 차지하기 : .

. 은 어떠한 문자 or 숫자이든 하나의 문자가 존재함을 의미합니다.

특정한 패턴이 있는 경우 (ex. 전화번호) . 을 이용해서 패턴을 만들어낼 수 있습니다.

이런 식으로 10 자리 전화번호의 경우를 추출해낼 수 있습니다.

 

 

2.4. 문자는 문자인데,, 메타 문자와 excape

이렇게 정규표현식의 패턴에 대해 알아봤는데요.

*, +, ., (), [], {} 와 같은 메타 문자들을 사용하여 패턴을 만들기도 했는데, 만약 정말 문자 그대로의 *, +, ., (), [], {}을 뽑아내고 싶다면 어떻게 해야할까요?

이 경우에는 백슬래시(\) 를 문자 앞에 붙혀줌으로써 원하는 결과값을 얻을 수 있습니다.

이 경우를 문자열을 escape 처리 해준다고 이야기 합니다.

위의 예시처럼 어떠한 문자가 3번 등장한 뒤, 마침표(.)로 끝나는 경우를 정규표현식으로 나타냈습니다.

 

 

---

3. SQL에서 정규표현식 활용해보기

지금까지 정규표현식의 패턴에 대해 알아봤으니 SQL 에서 어떻게 활용할 수 있는지 예시를 통해 알아봅시다.

 

3.1. 예제 1 : 특정 문자를 포함한 문자열 추출

< 문제 >
CITY 컬럼에서 값이 (a, e, i, o, u) 로 시작하고 끝나는 CITY만 가져오기

< 정규표현식을 사용할 때 >

select distinct city

from station

where city regexp "^[aeiou].*[aeiou]$"

SQL 에서 정규표현식을 사용할 땐 regexp 를 사용해주면 됩니다.

위 정규표현식을 해석해보면 다음과 같습니다.

정규표현식	해석
^[aeiou]	a,e,i,o,u 가 문자열의 제일 앞에 있으면서
.*	중간에 어떤 문제가 0개 이상 존재하고
[aeiou]$	문자열 가장 마지막에 a,e,i,o,u 가 존재하는 문자열 추출

단 한줄의 코드로 원하는 조건을 걸어둘 수 있는데요.

별 일이 아닌 것 같지만 정규표현식을 사용하지 않을 경우를 생각해보면 정규표현식에 대해 감사함을 느낄 수 있습니다.

 

< 정규표현식을 사용하지 않을 때 >

select distinct city

from station

where (city like "a%" and city like "%a")
	or (city like "a%" and city like "%e")
    or (city like "a%" and city like "%i")
    or (city like "a%" and city like "%o")
    or (city like "a%" and city like "%u")
    or (city like "e%" and city like "%a")
    ....
    or (city like "e%" and city like "%e")

이렇게 작성할 수 있는데요.

or 조건과 and 조건이 들어가면서 총 25개의 조건을 하나하나 써줘야하는 상황이 발생합니다.

 

3.2. 예제 2 : 특정 문자를 포함하지 않는 문자열 추출

< 문제 >
CITY 컬럼에서 값이 (a, e, i, o, u) 로 시작하지 않는 CITY만 가져오기

< 정규표현식을 사용할 때 >

select distinct city

from station

where city not regexp "^[aeiou].*"

정규표현식에서 어떤 값을 포함하지 않는 경우를 나타낼 땐 regexp 앞에 not 을 넣어주면 됩니다.

 

< 정규표현식을 사용하지 않을 때 >

select distinct city

from station

where city not like "a%"
    and city not like "e%"
    and city not like "i%"
    and city not like "o%"
    and city not like "u%"

정규표현식을 사용하지 않는다면 이렇게 여러번의 not like와 and 조건을 사용하게 됩니다.

 

 

---

4. 마치며

이렇게 정규표현식에 대해 알아봤는데요.

정규표현식을 사용해준다면 쿼리의 길이가 짧아지고 훨씬 간단하게 조건절을 작성할 수 있습니다.

하지만 정규표현식의 세계는 너무나도 넓어서 끝없는 활용이 가능한데요.

이 세계에 접근하는 마인드셋을 이야기하며 이번 포스팅을 마치려고 합니다.

 

정규표현식은 모래성 쌓기 게임같다.

출처 :&nbsp;https://www.youtube.com/watch?v=vV7LtLn-xhI

정규표현식을 사용하다보면 '내가 원하는 값이 아닌데 같이 추출되는 경우' 또는 '내가 원하는 값이 추출되지 않는 경우'를 겪게 됩니다.

보통은 이러한 순서로 문제가 발생하는데요.

1. 정규표현식을 활용했는데 원하지 않는 값이 나왔다.
2. 조금 더 조건을 넣어서 원치않는 값을 제거하려 노력한다.
3. 그랬더니 내가 원하는 값도 같이 제외가 되어버린다.
4. 그래서 다시 조건을 완화하며 내가 원하는 값이 나오도록 수정하지만, 또 원치않는 값이 같이 추출된다.
... (3,4번 반복..)

정규표현식은 "전체 문자열에서 제외하고싶은 문자열들을 하나하나 제거해가는 패턴을 넣으며 최종적인 결과를 만들어 내는 것"입니다.

그러니 아직까지 정규표현식에 익숙하지 않은 우리는 아이들이 모래성을 조심조심 제거해 깃발을 지켜내는 것 처럼,

한 번에 원하는 패턴을 만들어내기보다, 제외할 패턴을 조금씩 제거해나가며 원하는 값을 만들어내는 것으로 정규표현식의 세계에 접근하는 것이 좋다는 생각을 하며 이번 포스팅을 마칩니다.