SQL - 각 데이터베이스 별 정규식 사용법(Oracle/MySQL/MSSQL/PostgreSQL)

각 데이터베이스 별 정규식 사용방법에 대해서 알아보자
 
 

1. Oracle

Oracle Database는 REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE 네 가지 정규식 관련 함수를 제공한다. Oracle 정규식은 POSIX(Portable Operation System interface)를 따르며 Lookbehind를 지원하지 않는다.

 

• REGEXP_LIKE: 특정 패턴이 일치하는지 확인하는 조건문(WHERE 절)에서 사용
• REGEXP_INSTR: 정규식 패턴을 기반으로 문자열 내 특정 위치를 반환
• REGEXP_SUBSTR: 정규식 패턴을 사용하여 부분 문자열을 추출
• REGEXP_REPLACE: 정규식을 사용해 문자열의 일부분을 대체

-- 숫자가 아닌 모든 문자 제거
SELECT REGEXP_REPLACE('123-456-7890', '[^0-9]', '') AS phone_number FROM dual;

-- 특정 패턴 확인 (이메일 형식 확인)
SELECT CASE WHEN REGEXP_LIKE('test@example.com', '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
            THEN 'Valid Email' ELSE 'Invalid Email' END AS email_check
FROM dual;

-- 문자열에서 특정 패턴 위치 반환 (도메인 부분 위치)
SELECT REGEXP_INSTR('test@example.com', '@') AS at_symbol_pos FROM dual;

-- 정규식을 사용해 부분 문자열 추출 (도메인 추출)
SELECT REGEXP_SUBSTR('test@example.com', '@(.+)', 1, 1, NULL, 1) AS domain FROM dual;

 
 
 
 

2. MySQL

MySQL에서는 8.0부터 REGEXP_REPLACE 함수를 사용할 수 있으며, REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR 등의 함수도 지원한다. 기본적으로 ICU 라이브러리를 사용하며 일부 Lookahead 기능은 지원하나 Lookbehind는 지원하지 않는다.
 

• REGEXP_LIKE: 특정 패턴이 일치하는지 확인
• REGEXP_INSTR: 문자열에서 정규식 패턴이 나타나는 위치를 찾는다.
• REGEXP_SUBSTR: 정규식 패턴과 일치하는 부분 문자열을 추출
• REGEXP_REPLACE: 정규식을 사용하여 문자열의 일부분을 대체

-- 숫자가 아닌 모든 문자 제거
SELECT REGEXP_REPLACE('123-456-7890', '[^0-9]', '') AS phone_number;

-- 특정 패턴 확인 (숫자 포함 여부 확인)
SELECT '123ABC' REGEXP '[0-9]+' AS has_number;  -- 결과: 1 (일치), 0 (불일치)

-- 특정 패턴 위치 반환 (첫 번째 단어 위치)
SELECT REGEXP_INSTR('Hello World!', '\\b\\w+\\b') AS first_word_pos;

-- 정규식을 사용해 부분 문자열 추출 (첫 번째 단어 추출)
SELECT REGEXP_SUBSTR('Hello World!', '\\b\\w+\\b') AS first_word;

 

 

 

반응형

 

3. MSSQL

Microsoft SQL Server는 정규식을 직접 지원하지 않지만, LIKE 연산자와 일부 와일드카드 패턴으로 간단한 정규식을 구현할 수 있다. 복잡한 정규식이 필요한 경우 SQL Server 내에서 .NET CLR을 통해 정규식을 사용하는 방법이 있다.
 

• LIKE: %와 _ 와일드카드를 사용하여 간단한 패턴을 검색
• PATINDEX: 패턴과 일치하는 부분의 시작 위치를 반환

 

-- 간단한 패턴 확인 (LIKE로 이메일 형식 간단 확인)
SELECT 
    CASE WHEN email_column LIKE '%@%.%' THEN 'Valid Email' ELSE 'Invalid Email' END AS email_check
FROM your_table;

-- 특정 패턴 위치 확인 (PATINDEX로 특정 패턴 위치 반환)
SELECT PATINDEX('%@%', 'test@example.com') AS at_symbol_pos;

-- 정규식을 사용하려면 CLR을 활성화하고 .NET에서 정규식 함수를 추가해야 함

 
 
 

4. PostgreSQL

PostgreSQL는 ~ 연산자와 REGEXP_REPLACE, REGEXP_MATCHES 함수 등 다양한 정규식 관련 함수를 지원하며, POSIX 기반 정규식을 사용한다. PostgreSQL은 Lookahead와 Lookbehind를 포함한 고급 정규식을 지원한다.
 

• ~: 문자열에서 정규식을 매칭할 때 사용
• ~*: 대소문자를 구분하지 않고 정규식 패턴을 찾는다.
• REGEXP_REPLACE: 정규식을 사용하여 문자열의 일부분을 대체
• REGEXP_MATCHES: 패턴이 일치하는지 확인하여 배열 형태로 반환

-- 숫자가 아닌 모든 문자 제거
SELECT REGEXP_REPLACE('123-456-7890', '[^0-9]', '', 'g') AS phone_number;

-- 특정 패턴 확인 (숫자가 포함된지 확인)
SELECT '123ABC' ~ '[0-9]+' AS has_number;  -- 결과: true (일치), false (불일치)

-- 특정 패턴 위치 반환 (첫 번째 단어 위치)
SELECT POSITION((REGEXP_MATCHES('Hello World!', '\\b\\w+\\b'))[1] IN 'Hello World!') AS first_word_pos;

-- Lookbehind 및 Lookahead 예제 (특정 단어 앞뒤의 단어 찾기)
SELECT REGEXP_REPLACE('PostgreSQL is powerful', '(?<=PostgreSQL\\s)is\\s', 'was ') AS result;
-- 결과: "PostgreSQL was powerful"

 
 
 
 
 

데이터베이스	주요 정규식 함수/연산자	정규식 지원 수준	예시
Oracle	REGEXP_LIKE REGEXP_INSTR REGEXP_SUBSTR REGEXP_REPLACE	POSIX 지원 Lookbehind 미지원	REGEXP_REPLACE('text', '[^0-9]', '')
MySQL	REGEXP_LIKE REGEXP_INSTR REGEXP_SUBSTR REGEXP_REPLACE	POSIX 지원 일부 Lookahead 지원	REGEXP_REPLACE('text', '[0-9]', '')
MSSQL	LIKE PATINDEX	정규식 미지원 (기본) CLR로 복잡한 정규식 가능	LIKE '%pattern%'
PostgreSQL	~ ~* REGEXP_REPLACE REGEXP_MATCHES	POSIX Lookbehind 지원 Lookahead 지원	REGEXP_REPLACE('text', '[^0-9]', '')

 
 
 
끝.