[ORACLE] REGEXP_INSTR() 함수로 문자열 내 패턴 찾는 방법

[Oracle] REGEXP_INSTR

 

Oracle SQL에서 문자열 내 정규표현식에 일치하는 위치를 찾고자 할 때 REGEXP_INSTR() 함수를 사용합니다. 이 함수는 기존의 INSTR() 함수보다 훨씬 강력하고 유연하며, 실무 데이터 분석 및 유효성 검증 과정에서 매우 유용하게 쓰입니다.

1. REGEXP_INSTR 함수란?

REGEXP_INSTR()는 Oracle에서 문자열 내 정규표현식 패턴과 일치하는 부분의 시작 위치 또는 종료 위치를 반환하는 함수입니다.

REGEXP_INSTR(source_string, pattern [, position [, occurrence [, return_option [, match_parameter [, sub_expression]]]]])

• source_string: 검색 대상 문자열
• pattern: 찾을 정규표현식
• position: 검색 시작 위치 (기본값: 1)
• occurrence: 몇 번째 일치를 찾을지 (기본값: 1)
• return_option: 0 = 일치 시작 위치, 1 = 일치 종료 다음 위치
• match_parameter: 'i', 'c', 'n', 'm' 등
• sub_expression: 서브 그룹의 번호

2. 기본 사용 예제

예제 1: 첫 번째 숫자의 시작 위치 찾기

SELECT REGEXP_INSTR('abc123def', '\d') AS pos FROM dual;

결과: 4

예제 2: 두 번째 숫자 그룹의 위치

SELECT REGEXP_INSTR('abc123def456', '\d+', 1, 2) AS pos FROM dual;

결과: 10

예제 3: 공백의 위치

SELECT REGEXP_INSTR('Oracle SQL Tutorial', '\s') AS pos FROM dual;

결과: 7

3. 실무 활용 예제

예제 4: 이메일 도메인 시작 위치 찾기

SELECT REGEXP_INSTR('user@example.com', '@.*') AS pos FROM dual;

결과: 5

예제 5: 주민등록번호 구분자 위치

SELECT REGEXP_INSTR('970101-1234567', '-') AS pos FROM dual;

결과: 7

예제 6: 특정 단어 'data'의 종료 위치

SELECT REGEXP_INSTR('bigdata analysis', 'data', 1, 1, 1) AS end_pos FROM dual;

결과: 8

4. 고급 활용

예제 7: 서브 그룹 위치 반환

SELECT REGEXP_INSTR('ID:1234', '(\d+)', 1, 1, 0, NULL, 1) AS sub_expr_pos FROM dual;

결과: 4

예제 8: 대소문자 무시 검색

SELECT REGEXP_INSTR('Oracle sql ORACLE', 'oracle', 1, 2, 0, 'i') AS pos FROM dual;

결과: 14

5. REGEXP_INSTR vs INSTR 비교

항목	REGEXP_INSTR	INSTR
검색 방식	정규표현식	단순 문자열
복잡한 조건 검색	가능	불가능
성능	비교적 느림	빠름

6. 성능 고려사항

정규표현식 기반 함수는 계산량이 크므로, 대량 데이터에 반복 적용 시 성능 문제가 발생할 수 있습니다. WHERE절 조건보다는 SELECT절에서 사전 처리하는 방식이 효율적입니다.

7. 실무 팁

• 데이터 유효성 검증, 포맷 확인 시 활용
• 문자열 내 위치 기반 조건 분리 로직 구성 가능
• 정규표현식 그룹 번호를 통한 정밀 추적 가능

8. 결론

REGEXP_INSTR() 함수는 단순 문자열 검색을 넘어 복잡한 텍스트 분석을 가능하게 합니다. 정규표현식의 이해와 함께 적절히 활용하면 데이터 정제, 유효성 검사, 패턴 분석 등에 강력한 도구로 자리 잡을 수 있습니다.

9. 참고자료

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