[SQL] SQL 정규표현식 가이드

 

목차

 

1. 정규표현식이란?

정규표현식은 텍스트 데이터 중에서 원하는 조건과 일치하는 문자열을 찾기 위해 사용되는 일종의 패턴이다. 복잡한 문자열을 처리할 때 사용되며, 특정 규칙을 가진 문자의 조합을 쉽게 식별할 수 있게 해준다.

 

2. SQL에서의 정규표현식과 기본 예시

SQL에서 정규표현식을 사용하면 데이터 검색, 데이터 검증, 데이터 형식 및 데이터 정제 작업을 효율적으로 수행할 수 있다. 대부분의 현대 데이터베이스 관리 시스템(DBMS)은 정규표현식을 지원하며, 특히 Oracle, PostgreSQL, MySQL 등에서 다양한 정규표현식 함수를 제공한다. 먼저 기본 함수 REGEXP_LIKE 을 활용한 기본적인 예시를 살펴보자.

 

21 REGEXP_LIKE("문자열", "정규표현식", "일치옵션")

• "문자열"이 "정규 표현식"패턴과 일치할 경우 true 반환
• "문자열" 자리에는 특정 문자열이나 속성이 위치할 수 있음

 

22 예시 쿼리 :

SELECT 'Match' AS Result 
FROM test_table
WHERE REGEXP_LIKE(text, 'a.c');

 

• text 속성 문자열이 패턴 'a.c'와 일치하는지 확인하는 쿼리.
• 패턴 'a.c'에서 '.'는 어떤 한 문자를 의미.
• 'a.c'는 'a'로 시작하고 'c'로 끝나며, 사이에 어떤 한 문자가 있는 문자열과 일치

 

23 예시 테이블 test_table:

id	text
1	abc
2	axc
3	abx
4	afc

 

 

24 쿼리 결과: 

'abc', 'axc', 'afc'가 조건 'a.c'에 부합하여 결과 테이블에 'Match'로 표시된다.

Result

Match

Match

Match

 

 

 

 

 

3. 정규표현식 기본 문법

메타 문자: 메타 문자는 특별한 의미를 가지고 있어 정규표현식의 작성을 돕는다. 주로 사용되는 메타 문자는 다음과 같다.

 

기호	기능
.	임의의 단일 문자
^	문자열의 시작
$	문자열의 끝
*	앞의 문자가 0번 이상 반복
+	앞의 문자가 1번 이상 반복
?	앞의 문자가 0번 또는 1번 등장
{n}	앞의 문자가 n번 반복
{n,}	앞의 문자가 n번 이상 반복
{n,m}	앞의 문자가 최소 n번, 최대 m번 반복
[]	괄호 안에 있는 문자 중 하나
|	두 패턴 중 하나 선택 (OR 조건)
\	다음 문자를 리터럴 문자로 지정

 

 

예시	예시 설명
a.c	'a'와 'c' 사이에 어떤 문자든 올 수 있음
^abc	'abc'로 시작하는 문자열과 일치함
abc$	'abc'로 끝나는 문자열과 일치함
ab*c	'a' 다음에 'b'가 0번 이상 반복되고 'c'가 옴
ab+c	'a' 다음에 'b'가 1번 이상 반복되고 'c'가 옴
ab?c	'a' 다음에 'b'가 0번 또는 1번 나타나고 'c'가 옴
a{3}	'a'가 정확히 3번 반복됨
a{3,}	'a'가 최소 3번 이상 반복됨
a{2,5}	'a'가 최소 2번, 최대 5번 반복됨
[a-c]x	'a'부터 'c'까지의 문자 중 하나 뒤에 'x'가 옴
cat|dog	"cat"이나 "dog"를 포함하는 문자열
\^abc\$	문자열 "^abc$"과 정확히 일치함

 

4. SQL 정규표현식 함수

SQL에서 제공하는 정규표현식 관련 함수들은 데이터를 검색하고 조작하는 데 유용하다. 각 함수의 사용법과 예제를 통해 기능을 이해한다.

41 일치 여부: REGEXP_LIKE

• 이 함수는 문자열이 정규표현식과 일치하는지 여부를 확인한다. 일치하는 경우 TRUE를 반환한다.
• 사용법: REGEXP_LIKE(source, pattern, options)
• 예제: 다음 쿼리는 'example'이라는 문자열이 'ex'로 시작하는지 확인한다.

SELECT * FROM table WHERE REGEXP_LIKE(column, '^ex');

42 일치 위치: REGEXP_INSTR

• 이 함수는 문자열 내에서 특정 정규표현식과 일치하는 부분의 위치(인덱스)를 반환한다.
• 사용법: REGEXP_INSTR(source, pattern, start, occurrence, return_option, match_parameter)
• 예제: 다음 쿼리는 'example' 내에서 'am'이 나타나는 위치를 찾는다.

SELECT REGEXP_INSTR('example', 'am') FROM DUAL;

43 일치 문자: REGEXP_SUBSTR

• 이 함수는 문자열에서 정규표현식과 일치하는 부분 문자열을 추출한다.
• 사용법: REGEXP_SUBSTR(source, pattern, start, occurrence, match_parameter)
• 예제: 다음 쿼리는 'example'에서 'ex'로 시작하는 부분 문자열을 반환한다.

SELECT REGEXP_SUBSTR('example', 'ex') FROM DUAL;

 

44 일치 대체: REGEXP_REPLACE

• 이 함수는 문자열에서 정규표현식과 일치하는 부분을 다른 문자열로 대체한다.
• 사용법: REGEXP_REPLACE(source, pattern, replace, start, occurrence, match_parameter)
• 예제: 다음 쿼리는 'example'에서 'ex'를 'EX'로 대체한다.

SELECT REGEXP_REPLACE('example', 'ex', 'EX') FROM DUAL;

 

이러한 함수들을 사용함으로써, SQL에서 데이터를 효과적으로 처리하고, 필요한 정보를 추출하거나 수정할 수 있다. 각 함수는 특정한 용도로 설계되어 있으며, 상황에 따라 적절히 선택하여 사용한다.

 

 

 

 

5 실제 사례를 통한 문제/예제

이 섹션에서는 SQL 정규표현식을 활용하여 다양한 문제를 해결하는 예제들을 제공한다. 각 항목별로 세 가지씩 문제를 만들고 이를 풀어본다.

 

 

51 문자열 검색(이메일 형식 검사)

문제 1: 이메일 주소가 '@' 기호를 포함하고, '.'으로 끝나지 않는 경우를 찾는다.

SELECT email
FROM users
WHERE REGEXP_LIKE(email, '^.+@.+\\.[^\\.]+$');

 

문제 2: 이메일 주소 중에 도메인이 'example.com'인 이메일만 검색한다.

SELECT email
FROM users
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@example\\.com$');

 

문제 3: 이메일 주소에 숫자가 포함되지 않은 경우를 찾는다.

SELECT email
FROM users
WHERE REGEXP_LIKE(email, '^[^0-9]+@[^0-9]+\\.[^0-9]+$');

 

 

52 데이터 검증 (전화번호 형식 검증)

 

문제 1: 전화번호가 국제 형식(+국가코드-지역코드-번호)을 따르지 않는 경우를 찾는다.

SELECT phone_number
FROM contacts
WHERE NOT REGEXP_LIKE(phone_number, '^\\+\\d{1,3}-\\d{1,4}-\\d{6,8}$');

 

문제 2: 전화번호 중에서 지역 코드가 02인 서울 지역 전화번호를 찾는다.

SELECT phone_number
FROM contacts
WHERE REGEXP_LIKE(phone_number, '^02-\\d{7,8}$');

 

문제 3: 전화번호에서 하이픈('-')을 제거하고 순수 숫자만 표시한다.

SELECT REGEXP_REPLACE(phone_number, '-', '') AS pure_number
FROM contacts;

 

 

53 데이터 추출 (로그 파일에서 날짜 추출)

문제 1: 로그에서 'YYYY/MM/DD' 형식의 날짜를 추출한다.

SELECT log_entry,
       REGEXP_SUBSTR(log_entry, '\\d{4}/\\d{2}/\\d{2}') AS extracted_date
FROM logs;

 

문제 2: 로그 엔트리 중에서 시간 정보(HH:MM:SS)만을 추출한다.

SELECT log_entry,
       REGEXP_SUBSTR(log_entry, '\\d{2}:\\d{2}:\\d{2}') AS extracted_time
FROM logs;

 

문제 3: 로그 파일에서 'ERROR'라는 단어가 포함된 모든 엔트리를 찾는다.

SELECT log_entry
FROM logs
WHERE REGEXP_LIKE(log_entry, 'ERROR');

 

 

54 데이터 치환 (문자열에서 특정 단어 치환)

 

문제 1: 사용자의 코멘트에서 'bug'라는 단어를 'feature'로 치환한다.

SELECT comments,
       REGEXP_REPLACE(comments, 'bug', 'feature') AS modified_comments
FROM feedback;

 

문제 2: 텍스트에서 모든 소문자를 대문자로 변환한다.

SELECT comments,
       REGEXP_REPLACE(comments, '([a-z])', UPPER('\\1')) AS upper_comments
FROM feedback;

 

문제 3: 주소에서 도로명만 추출한다. (예: '123 Baker St, New York'에서 'Baker St' 추출)

SELECT address,
       REGEXP_SUBSTR(address, '[A-Za-z\\s]+St') AS street_name
FROM addresses;

 

각 문제는 SQL 정규표현식을 활용하여 다양한 데이터 처리 요구사항을 충족시키는 방법을 보여준다.

 

 

 

6 정규표현식 성능 최적화 팁

정규표현식은 강력하지만, 잘못 사용하면 데이터베이스 성능에 부정적인 영향을 미칠 수 있다. 성능을 최적화하기 위한 몇 가지 팁은 다음과 같다.

• 최소화 원칙 사용: 가능한 가장 간단하고 짧은 패턴을 사용한다. 복잡한 패턴은 처리 시간을 늘릴 수 있다.
• 특정 타입의 문자 클래스 사용: 가능하면 . 대신 구체적인 문자 클래스([a-z], [0-9] 등)를 사용한다. 이는 검색 범위를 좁혀 성능을 향상시킨다.
• 앵커 사용하기: 문자열의 시작(^)과 끝($)에 앵커를 사용하여 검색 범위를 제한한다. 이는 불필요한 중간 위치 검색을 방지한다.
• 필요한 부분만 처리: 필요한 데이터 부분에만 정규표현식을 적용하고, 전체 데이터셋에 적용하지 않는다.
• 재사용 가능한 패턴: 정규표현식을 컴파일하고 재사용 가능하게 만들어 처리 속도를 높인다.

이러한 최적화 기법은 데이터베이스의 부하를 줄이고, 처리 속도를 향상시키는 데 도움을 줄 수 있다.

 

 

 

7 결론

SQL 정규표현식은 데이터 검색, 검증, 추출 및 수정과 같은 다양한 작업에서 유용하게 사용된다. 본 글에서는 SQL 정규표현식의 기본 문법, 사용 가능한 함수, 그리고 구체적인 예제를 통해 그 사용법을 상세히 설명하였다. 또한, 성능 최적화 팁을 제공하여 실제 작업 환경에서 정규표현식의 효율을 높일 수 있는 방법을 제시하였다. 위의 지식은 데이터를 효과적으로 관리하고, 처리하는 데 있어 중요한 역할을 한다. 이러한 SQL 정규표현식을 잘 이해하고 적절히 활용하는 것은 모든 데이터 전문가에게 필수적인 기술이다.