[ORACLE] REGEXP_SUBSTR() 함수로 텍스트 분리하는 실무 활용 법

[Oracle] REGEXP_SUBSTR

 

Oracle SQL에서 복잡한 문자열 패턴을 추출하기 위해 사용하는 대표적인 함수 중 하나가 REGEXP_SUBSTR()입니다. 이 함수는 정규표현식을 기반으로 동작하며, 단순한 SUBSTR() 함수보다 훨씬 유연하게 다양한 텍스트를 처리할 수 있습니다. 본 글에서는 REGEXP_SUBSTR 함수의 원리, 문법, 실무 활용법까지 상세히 설명합니다.

1. REGEXP_SUBSTR 함수란?

REGEXP_SUBSTR() 함수는 Oracle에서 문자열에서 정규표현식 패턴에 일치하는 하위 문자열을 추출하는 함수입니다.

REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, return_option [, match_parameter]]]])

• source_string: 원본 문자열
• pattern: 추출할 정규표현식
• position: 검색을 시작할 문자 위치 (기본값: 1)
• occurrence: 몇 번째 일치 항목을 추출할지 (기본값: 1)
• return_option: 0은 전체 일치, 1은 하위 그룹 반환
• match_parameter: 정규표현식 옵션 ('i', 'c', 'n' 등)

2. 기본 사용 예제

예제 1: 이메일 주소에서 도메인 추출

SELECT REGEXP_SUBSTR('user@example.com', '@(.*)$', 1, 1, NULL, 1) AS domain FROM dual;

결과: 'example.com'

예제 2: 숫자만 추출

SELECT REGEXP_SUBSTR('abc123def', '\d+', 1, 1) AS number FROM dual;

결과: '123'

예제 3: 첫 번째 영문자 추출

SELECT REGEXP_SUBSTR('2025년_A버전', '[A-Za-z]+', 1, 1) AS letter FROM dual;

결과: 'A'

3. 실무 활용 예제

예제 4: 문자열에서 첫 번째 IP 주소 추출

SELECT REGEXP_SUBSTR(
  '접속 IP는 192.168.1.1입니다.',
  '\d{1,3}(\.\d{1,3}){3}',
  1,
  1
) AS ip_address
FROM dual;
  

결과: '192.168.1.1'

예제 5: 콤마로 구분된 세 번째 단어 추출

SELECT REGEXP_SUBSTR('사과,바나나,포도,귤', '[^,]+', 1, 3) AS third_word FROM dual;
  

결과: '포도'

예제 6: 주민등록번호에서 생년월일 추출

SELECT REGEXP_SUBSTR('970101-1234567', '^\d{6}') AS birth FROM dual;
  

결과: '970101'

4. 매개변수 조합 실전 예

예제 7: 두 번째 영문자 그룹 추출

SELECT REGEXP_SUBSTR('A1B2C3D4', '[A-Z]', 1, 2) AS second_letter FROM dual;
  

결과: 'B'

5. REGEXP_SUBSTR vs SUBSTR

항목	REGEXP_SUBSTR	SUBSTR
기능	정규표현식 기반 추출	위치 기반 추출
유연성	높음 (패턴 매칭)	낮음
사용 난이도	비교적 높음	낮음

6. 성능 주의사항

정규표현식은 강력하지만 비용이 큰 연산입니다. 특히 대용량 데이터에서 반복적으로 REGEXP_SUBSTR를 사용하면 성능 저하가 발생할 수 있으므로, 가급적 인덱스가 없는 칼럼이나 전처리 단계에서 활용하는 것이 좋습니다.

7. 실무 팁

• 전화번호, 이메일, 코드 등 패턴이 명확한 데이터에서 효과적
• 단일 칼럼 내 다중 데이터 분리 처리에 적합
• 테스트 시 REGEXP_LIKE와 함께 활용 가능

8. 결론

Oracle의 REGEXP_SUBSTR 함수는 복잡한 패턴 기반 데이터 추출에 매우 유용합니다. 특히 고급 데이터 정제와 유효성 검증 작업에서 강력한 도구가 될 수 있습니다. 다양한 예제를 바탕으로, 이 함수를 실무에 적극 활용해 보시길 바랍니다.

9. 참고자료

• Oracle 공식 문서: https://docs.oracle.com
• Oracle SQL Language Reference 21c
• SQL 정규표현식 실무 활용 가이드 - 김상형 저