REGEXP

개념

REGEXP 함수는 정규 표현식을 사용하여 문자열을 처리하는 SQL 함수이다. 특정 패턴을 기반으로 텍스트를 검색하건, 데이터를 검증하고, 문자열을 치환하는데 활용된다.

Oracle REGEXP 함수 5가지

1. REGEXP_LIKE - 패턴 매칭

WHERE 절에서 패턴 일치 여부 확인

-- 기본 사용법
SELECT * FROM employees
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$');

-- 대소문자 무시 옵션 ('i')
SELECT * FROM employees
WHERE REGEXP_LIKE(name, '^kim', 'i');

-- 이름이 'A'로 시작하는 직원
SELECT * FROM employees
WHERE REGEXP_LIKE(first_name, '^A');

구문: REGEXP_LIKE(source_string, pattern [, match_parameter])

match_parameter 옵션:

• 'i': 대소문자 무시
• 'c': 대소문자 구분 (기본값)
• 'n': .이 개행 문자도 매칭
• 'm': 다중 라인 모드

2. REGEXP_REPLACE - 패턴 치환

패턴과 일치하는 부분을 다른 문자열로 교체

-- 전화번호에서 특수문자 제거
SELECT 
    phone,
    REGEXP_REPLACE(phone, '[^0-9]', '') AS clean_phone
FROM contacts;

-- 여러 공백을 하나로
SELECT REGEXP_REPLACE('Hello    World', ' +', ' ') AS result FROM DUAL;
-- 결과: 'Hello World'

-- 이메일 마스킹 (앞 3자리만 보이기)
SELECT 
    email,
    REGEXP_REPLACE(email, '^(.{3})(.*)(@.*)$', '\1***\3') AS masked_email
FROM users;

구문: REGEXP_REPLACE(source_string, pattern [, replace_string [, position [, occurrence [, match_parameter]]]])

3. REGEXP_SUBSTR - 패턴 추출

패턴과 일치하는 부분 문자열 추출

-- 이메일에서 사용자명 추출
SELECT 
    email,
    REGEXP_SUBSTR(email, '^[^@]+') AS username
FROM users;

-- 이메일에서 도메인 추출
SELECT 
    email,
    REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1) AS domain
FROM users;

-- 문자열에서 첫 번째 숫자 추출
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+') AS first_number FROM DUAL;
-- 결과: '123'

-- 문자열에서 두 번째 숫자 추출
SELECT REGEXP_SUBSTR('abc123def456', '[0-9]+', 1, 2) AS second_number FROM DUAL;
-- 결과: '456'

구문: REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter [, subexpression]]]])

4. REGEXP_INSTR - 패턴 위치

패턴이 나타나는 위치 반환

-- 첫 번째 숫자의 위치 찾기
SELECT REGEXP_INSTR('abc123def', '[0-9]') AS position FROM DUAL;
-- 결과: 4

-- 이메일에서 @ 위치 찾기
SELECT 
    email,
    REGEXP_INSTR(email, '@') AS at_position
FROM users;

-- 두 번째 공백의 위치
SELECT REGEXP_INSTR('Hello World Test', ' ', 1, 2) AS position FROM DUAL;
-- 결과: 12

-- 패턴의 끝 위치 반환 (return_option = 1)
SELECT REGEXP_INSTR('abc123def', '[0-9]+', 1, 1, 1) AS end_position FROM DUAL;
-- 결과: 7

구문: REGEXP_INSTR(source_string, pattern [, position [, occurrence [, return_option [, match_parameter [, subexpression]]]]])

5. REGEXP_COUNT - 패턴 개수

패턴이 나타나는 횟수 세기

-- 문자열에서 'o' 개수
SELECT REGEXP_COUNT('hello world', 'o') AS count FROM DUAL;
-- 결과: 2

-- 문자열에서 숫자 개수
SELECT REGEXP_COUNT('abc123def456', '[0-9]') AS count FROM DUAL;
-- 결과: 6

-- 이메일에서 점(.) 개수
SELECT 
    email,
    REGEXP_COUNT(email, '\.') AS dot_count
FROM users;

구문: REGEXP_COUNT(source_string, pattern [, position [, match_parameter]])

자주 사용하는 정규표현식 패턴

-- 1. 이메일 검증
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')

-- 2. 전화번호 (010-1234-5678)
WHERE REGEXP_LIKE(phone, '^01[0-9]-[0-9]{4}-[0-9]{4}$')

-- 3. 휴대폰 (010으로 시작하는 11자리)
WHERE REGEXP_LIKE(mobile, '^010[0-9]{8}$')

-- 4. 숫자만
WHERE REGEXP_LIKE(code, '^[0-9]+$')

-- 5. 영문자만
WHERE REGEXP_LIKE(name, '^[A-Za-z]+$')

-- 6. 영문+숫자
WHERE REGEXP_LIKE(username, '^[A-Za-z0-9]+$')

-- 7. 한글만 (Unicode 범위)
WHERE REGEXP_LIKE(name, '^[가-힣]+$')

-- 8. 주민등록번호 (6자리-7자리)
WHERE REGEXP_LIKE(ssn, '^[0-9]{6}-[0-9]{7}$')

-- 9. 우편번호 (5자리)
WHERE REGEXP_LIKE(zipcode, '^[0-9]{5}$')

-- 10. IP 주소
WHERE REGEXP_LIKE(ip, '^([0-9]{1,3}\.){3}[0-9]{1,3}$')

-- 11. URL
WHERE REGEXP_LIKE(url, '^https?://[A-Za-z0-9.-]+\.[A-Za-z]{2,}')

-- 12. 비밀번호 (영문+숫자+특수문자, 8-20자)
WHERE REGEXP_LIKE(password, '^(?=.*[A-Za-z])(?=.*[0-9])(?=.*[@$!%*#?&])[A-Za-z0-9@$!%*#?&]{8,20}$')

실전 예제

예제 1: 유효한 이메일 찾기

SELECT employee_id, email
FROM employees
WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

예제 2: 전화번호 포맷 변경

-- 010-1234-5678 → 01012345678
SELECT 
    phone,
    REGEXP_REPLACE(phone, '-', '') AS formatted_phone
FROM contacts;

-- 01012345678 → 010-1234-5678
SELECT 
    phone,
    REGEXP_REPLACE(phone, '([0-9]{3})([0-9]{4})([0-9]{4})', '\1-\2-\3') AS formatted_phone
FROM contacts
WHERE REGEXP_LIKE(phone, '^[0-9]{11}$');

예제 3: 이메일 도메인별 그룹화

SELECT 
    REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1) AS domain,
    COUNT(*) AS user_count
FROM users
GROUP BY REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1)
ORDER BY user_count DESC;

예제 4: 주민등록번호 검증 및 마스킹

-- 검증
SELECT *
FROM members
WHERE REGEXP_LIKE(ssn, '^[0-9]{6}-[1-4][0-9]{6}$');

-- 마스킹 (뒷자리 숨김)
SELECT 
    name,
    REGEXP_REPLACE(ssn, '(-[0-9]{7})$', '-*******') AS masked_ssn
FROM members;

예제 5: 특정 패턴의 제품 코드 찾기

-- 제품 코드: 영문 2자 + 숫자 4자 (예: AB1234)
SELECT product_name, product_code
FROM products
WHERE REGEXP_LIKE(product_code, '^[A-Z]{2}[0-9]{4}$');

예제 6: 문자열에서 모든 숫자 추출

SELECT 
    description,
    REGEXP_REPLACE(description, '[^0-9]', '') AS numbers_only
FROM products;

예제 7: 여러 공백을 하나로 정리

SELECT 
    address,
    REGEXP_REPLACE(address, '( ){2,}', ' ') AS clean_address
FROM locations;

예제 8: URL에서 프로토콜 제거

SELECT 
    url,
    REGEXP_REPLACE(url, '^https?://', '') AS url_without_protocol
FROM websites;

성능 최적화 팁

1. 함수 기반 인덱스 생성

CREATE INDEX idx_email_domain ON users(REGEXP_SUBSTR(email, '@(.+)$', 1, 1, NULL, 1));

2. LIKE와 REGEXP_LIKE 혼용

-- 성능 개선: LIKE로 먼저 필터링
SELECT *
FROM users
WHERE email LIKE '%@gmail.com'
  AND REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@gmail\.com$');

3. 복잡한 패턴은 여러 단계로 분리

-- 나쁜 예
WHERE REGEXP_LIKE(column, '매우_복잡한_정규식_패턴')

-- 좋은 예
WHERE column LIKE '%keyword%'
  AND REGEXP_LIKE(column, '간단한_패턴')

정규표현식은 강력하지만 성능에 영향을 줄 수 있으므로, 대용량 데이터에서는 신중하게 사용해야 합니다!