Oracle 불필요한 문자 제거, REGEXP_REPLACE 사용법

오라클 데이터베이스에서 텍스트 데이터에 특정 문자를 제거하고 필요한 문자들만 남겨야하는 경우가 종종 있습니다. 마침표, 콤마, 쌍다옴표 등 불필요한 특수 문자는 제거하고 텍스트와 숫자, 특정 문자(예: #)를 유지해야 하는 경우를 예를 들어서 단계별로 진행해보겠습니다.

 

1. 오라클의 REGEXP_REPLACE 함수는?

REGEXP_REPLACE 함수는 정규식을 사용해 데이터에서 특정 패턴을 찾아 제거하거나 다른 문자열로 치환할 수 있는 기능을 제공합니다.

 

기본 문법

REGEXP_REPLACE(텍스트_컬럼, '정규식 패턴', '대체할 문자열')

 

2. 문제 상황

• 데이터 : "안녕하세요, #5846", "Hello, ....456.78"
• 요구 사항
  • 마침표(.), 콤마(,_), 쌍따옴표("), 괄호(()) 불필요한 특수 문자를 제거
  • 특수문자 (#)와 숫자 (0-9), 텍스트는 유지

3. 문제 해결하기

SELECT REGEXP_REPLACE(텍스트_컬럼, '[,."''()]', '') AS 정리된_텍스트
FROM 테이블_이름;

• REGEXP_REPLACE 함수 : 정규식을 사용해 특정 패턴의 문자를 찾아 제거합니다.
• [,.\"'()] : 제거 대상이 되는 문자들을 정규식 패턴으로 정의한 부분입니다. 
• '' : 제거된 부분을 빈 문자열로 대체합니다.

4. 실행 결과

원본 텍스트	처리된 텍스트
"안녕하세요, #5846"	안녕하세요 #5846
"Hello, ....456.78"	Hello 45678

 

6. 정규식 (Regular Expression)

정규식은 문자열에서 특정 패턴을 찾아내고, 변환하거나 검증하는데 사용됩니다. 텍스트에서 특정한 규칙을 가진 부분을 찾거나 수정할 때 주로 사용되며, 다양한 프로그래밍 언어와 데이터베이스 시스템에서 지원됩니다.

오라클에서도 정규식을 활용한 함수는 REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR 등이 있습니다.

 

1) 문자 클래스 : 특정 문자나 범위를 지정합니다.

• [a-z] : 소문자 알파벳 전체와 매칭
• [0-9] : 숫자 전체와 매칭
• [^abc] : a,b,c를 제외한 모든 문자와 매칭
• [^0-9] : 숫자를 제외한 모든 문자와 매칭

2) 메타 문자 : 특정 의미를 가지는 특수 기호입니다.

• . : 임의의 한 문자와 매칭
• ^ : 문자열의 시작
• $ : 문자열의 끝
• * : 0번 이상 반복
• + : 1번 이상 반복
• ? : 0번 또는 1번 존재
• {n} : 정확히 n번 반복
• {n,} : 최소 n번 반복
• {n,m} : n번 이상, m번 이하 반복

3) 이스케이프 문자 : 특수 문자를 일반 문자로 사용하려면 \로 이스케이프해야 합니다.

• \. : 마침표 문자 자체와 매칭
• \(, \) : 괄호 문자 자체와 매칭
• \\ : 역슬래시 문자 자체와 매칭

5. 정규식 패턴 확장하기

SELECT REGEXP_REPLACE(텍스트_컬럼, '[,."''();?]', '') AS 정리된_텍스트
FROM 테이블_이름;

• 추가적으로 제거할 문자가 있을 경우 패턴에 추가하면 됩니다.
• 예를 들어 세미콜론(;)과 물음표(?)라면 위에 코드와 같이 수정하면 됩니다.

SELECT REGEXP_REPLACE(텍스트_컬럼, '[^a-zA-Z0-9#]', ' ') AS 정리된_텍스트
FROM 테이블_이름;

• 모든 특수 문자를 제거하고 텍스트와 숫자만 남기려면 위와 같은 패턴으로 수정합니다.
• [^a-zA-Z0-9#]: 영어 대소문자(a-z, A-Z), 숫자(0-9), # 기호를 제외한 모든 문자를 의미합니다.
• 예 : "안녕하세요, ABC #5846" -> ABC #5846

7. 주의사항

• 정규식 패턴의 정확성
  • REGEXP_REPLACE에서 사용하는 정규식 패턴이 잘못 정의되면 원치 않는 데이터가 삭제될 수 있습니다. 테스트 데이터를 사용해 충분히 검증한 후 실제 데이터를 처리해야 합니다.
• 대용량 대이터 처리
  • 대용량 데이터를 처리할 경우, 정규식을 사용하는 함수는 성능에 영향을 줄 수 있습니다. 필요한 경우 인덱스와 병렬 처리를 활용하거나 PL/SQL로 최적화하세요.

이번 글에서는 특정 문자를 제거하는 것부터 패턴 매칭을 활용한 데이터 정리 방법까지 살펴보았습니다. 이를 적용하여 데이터 정리 작업에 도움이 되길 바랍니다!