[Oracle] 오라클 REGEXP_REPLACE 함수 사용법

오라클 SQL에서 정규식을 사용하여 치환(REPLACE)을 하기 위해서 REGEX_REPLACE 함수를 사용하면 된다. 정규식을 사용하면 공백, 숫자, 특수문자 등을 쉽게 제거할 수 있으며, 특정 문자열 패턴으로 치환이 가능하기 때문에 아주 유용하게 사용할 수 있다. REGEXP_REPLACE 함수를 사용할 때는 역참조라는 방법을 이해하면 조금 더 다양하게 활용할 수 있으므로 꼭 익혀두는 것이 좋다.

 

REGEXP_REPLACE("문자열", "정규표현식", "치환문자열", "검색시작위치", "매칭순번", "일치옵션")

 

목차 기본 사용법 다중(여러개) 치환 방법 공백, 특수문자 제거 방법 역참조를 사용하여 치환 방법 마스킹 처리 방법 기타 옵션 사용법

 

기본 사용법

SELECT REPLACE('Oracle Database 23c', 'Oracle', '******')        AS "REPLACE"
     , REGEXP_REPLACE('Oracle Database 23c', 'Oracle', '******') AS "REGEXP_REPLACE"
  FROM dual

 

 

단순 문자열을 치환할 때는 기본 REPLACE 함수와 정규식 함수 REGEXP_REPLACE 함수의 결과는 동일한다.

 

SELECT REPLACE('Oracle Database 23c', 'oracle', '******')                   AS "REPLACE"
     , REGEXP_REPLACE('Oracle Database 23c', 'oracle', '******', 1, 0, 'i') AS "REGEXP_REPLACE"
  FROM dual

 

 

기존 REPLACE 함수에서는 대소문자 구분 없이 특정 문자열을 찾아서 치환하지 못하지만, REGEXP_REPLACE 함수에서는 "i" 옵션을 부여하면 대소문자 구분 없이 문자열을 찾아서 치환할 수 있다.

 

SELECT REGEXP_REPLACE('Oracle Database 23c', '[0-9]', '*') AS result1
  FROM dual

 

 

[0-9] 정규식을 사용하면 대상 문자열에서 모든 숫자를 "*"로 치환한다. 

[0-9], \d, [[:digit:]]는 모두 숫자를 의미하며, 자신에게 익숙한 형식을 사용하면 된다.

 

SELECT REGEXP_REPLACE('Oracle Database 23c', '[[:alpha:]]', '*') AS result1
  FROM dual

 

 

[[:alpha:]] 정규식을 사용하면 대상 문자열에서 모든 알파벳을 "*"로 치환한다.

 

[[:alpha:]], [a-zA-Z]는 모든 알파벳을 의미하며, 자신에게 익숙한 형식을 사용하면 된다.

[a-z]는 소문자, [A-Z]는 대문자만 찾을 때 사용하면 된다.

 

SELECT REGEXP_REPLACE('Oracle Database 23c', '[A-Z]{1}[a-z]{3}', '****') AS result1
  FROM dual

 

 

[A-Z]{1}[a-z]{3}는 대문자 1자와 소문자 3자로 된 문자열을 "****"로 치환한다.

 

중괄호( { } )를 사용하여 앞의 문자 개수를 지정할 수 있다.

{1}는 1자 {1,2} 1자~2자, {1,} 1자 이상을 의미한다.

 

다중(여러개) 치환 방법

SELECT REGEXP_REPLACE('Oracle Database 23c Release', 'Orac|base|Rele', '****') AS res
  FROM dual

 

 

REGEXP_REPLACE 함수를 사용하면 여러 개의 문자열 또는 패턴을 한 번에 치환할 수 있다. 기본 REPLACE 함수를 사용하여 다중치환을 할 때는 중첩으로 사용해야 하기 때문에 구문이 길어지는데 REGEXP_REPLACE 함수를 사용하면 한 번에 다중 치환이 가능하다.

 

WITH temp AS
(
    SELECT 'oracle9i Database Release 2'   str FROM dual UNION ALL
    SELECT 'Oracle Database 11g Release 1' str FROM dual UNION ALL
    SELECT 'Oracle Database 23c'           str FROM dual 
)

SELECT str
     , REGEXP_REPLACE(str, '^[A-Z]{1}[a-z]{2}|[0-9]+[igc]|Rel', '***') AS result1
  FROM temp

 

 

다중치환 또한 정규식 패턴을 사용하여 치환할 수 있다.

 

공백, 특수문자 제거 방법

SELECT REGEXP_REPLACE('Oracle  Database   23c', ' ')            AS result1
     , REGEXP_REPLACE('Oracle  Database   23c', '( ){2,}', ' ') AS result2
  FROM dual

 

 

특정 문자열을 제거할 때는 REGEXP_REPLACE("대상문자열", " ")와 REGEXP_REPLACE("대상문자열", " ", "")은 동일한 결과를 반환한다. 3번째 인자는 제외해도 해당 문자열을 제거할 수 있다.

 

( ){2,}은 2개 이상의 공백이 있을 경우, 하나의 공백으로 치환하는 예제이다.

 

SELECT REGEXP_REPLACE('Oracle_Database_(23c)###', '[[:punct:]]') AS result1
     , REGEXP_REPLACE('Oracle_Database_(23c)###', '[!@#$%&,_]')  AS result2
  FROM dual

 

 

[[:punct:]]은 모든 특수문자를 의미한다. (공백 문자는 특수문자 아님)

특정 특수문자만 제거하고 싶을 때는 문자 클래스( [ ] )에 제거할 특수문자만 입력하면 된다.

 

SELECT REGEXP_REPLACE('오라클, "Oracle Database 23c" 버전', '[^0-9a-zA-Z]') AS result1
     , REGEXP_REPLACE('오라클, "Oracle Database 23c" 버전', '[가-힣]')      AS result2
  FROM dual

 

 

문자 클래스( [ ] )의 첫 부분에 캐럿( ^ ) 기호를 사용하면, 문자 클래스 내부의 문자를 제외한 문자를 의미한다.

[^0-9a-zA-Z]은 숫자와 알파벳 문자를 제외한 모든 문자를 의미하며, 한글과 특수문자, 공백이 제거된다.

 

[가-힣]은 한글을 찾을 때 사용할 수 있다.

 

역참조를 사용하여 치환 방법

SELECT REGEXP_REPLACE('Oracle Database 23c', 'Database', '********')     AS result1
     , REGEXP_REPLACE('Oracle Database 23c', '(Data)(base)', '********') AS result2
     , REGEXP_REPLACE('Oracle Database 23c', '(Data)(base)', '\1****')   AS result3
     , REGEXP_REPLACE('Oracle Database 23c', '(Data)(base)', '****\2')   AS result4     
  FROM dual

 

 

REGEXP_REPLACE 함수를 사용할 때 역참조를 이해하고 있으면 다양 작업을 할 수 있다.

 

역참조를 하기 위해서는 정규식 패턴 부분에 괄호를 사용하여 그룹을 만들면 된다. 그룹(괄호) 순서대로 \1, \2, \3, \4 ... 순번을 사용하여, 그룹에 해당하는 문자열을 가져와서 사용하면 된다.

 

SELECT REGEXP_REPLACE('20240317', '([0-9]{4})([0-9]{2})([0-9]{2})', '\1-\2-\3') AS result1
     , REGEXP_REPLACE('01012345678', '(\d{3})(\d{4})(\d{4})', '\1-\2-\3')       AS result2
  FROM dual

 

 

(숫자 4자), (숫자 2자), (숫자 2자)인 문자열이 있을 경우, 해당 패턴 그룹을 치환문자열에서 하이픈을 넣어서 치환한다.

 

WITH temp AS 
(
  SELECT '오라클은 2023.04.04에 '
      || 'Oracle Database 23c '
      || '릴리스 되었다. - 2024/03/17' AS str
    FROM dual
)

SELECT REGEXP_REPLACE(str, '(\d{4})[./](\d{2})[./](\d{2})', '\1년\2월\3일') AS result1
  FROM temp

 

 

문자 클래스( [ ] )에 문자를 입력한 경우, 모든 문자는 문자 하나하나 개별 문자로 인식한다.

패턴 그룹의 문자열에 년, 월, 일을 붙여서 치환한다.

 

마스킹 처리 방법

WITH temp AS
(
    SELECT 'MILLER' ename FROM dual UNION ALL
    SELECT 'JONES'  ename FROM dual UNION ALL
    SELECT 'SMITH'  ename FROM dual UNION ALL
    SELECT 'ADAMS'  ename FROM dual UNION ALL
    SELECT 'JAMES'  ename FROM dual
)

SELECT REGEXP_REPLACE(ename, '(^[A-Z]{1})([A-Z]{2})([A-Z]*$)', '\1**\3') AS result1
  FROM temp

 

 

REGEXP_REPLACE 함수를 사용하면 문자열의 마스킹 처리를 쉽게 할 수 있다.

 

문자 클래스( [ ] ) 내부에 캐럿( ^ )을 사용할 경우 문자 클래의 부정 문자셋이 되지만, 문자 클래스 밖에서 캐럿을 사용할 경우 문자열의 시작을 의미한다.

 

위의 예시에서 ^~~~$는 문자열의 시작과 끝을 의미하여, 정규식에서 앵커라고 부른다.

 

WITH temp AS 
(
  SELECT '고객센터 02-9876-5432, '
      || '담당자 010-1234-5678 입니다' AS str
    FROM dual
)

SELECT REGEXP_REPLACE(str, '(\d{2,3})-(\d{4})-(\d{4})', '\1-\2-****') AS result1
  FROM temp

 

 

REGEXP_REPLACE 함수를 사용하여 전화번호도 쉽게 마스킹 처리 할 수 있다.

 

기타 옵션 사용법

SELECT REGEXP_REPLACE('Oracle Database 23c', '[[:alpha:]]{3}', '***')    AS result1
     , REGEXP_REPLACE('Oracle Database 23c', '[[:alpha:]]{3}', '***', 4) AS result2
     , REGEXP_REPLACE('Oracle Database 23c', '[[:alpha:]]{3}', '***', 8) AS result3
  FROM dual

 

 

REGEXP_REPLACE 함수에서 4번째 인자는 문자열에서 검색을 시작할 위치를 의미한다.

 

SELECT REGEXP_REPLACE('Oracle Database 23c', '[[:alpha:]]{3}', '***', 1, 1) AS result1
     , REGEXP_REPLACE('Oracle Database 23c', '[[:alpha:]]{3}', '***', 1, 2) AS result2
     , REGEXP_REPLACE('Oracle Database 23c', '[[:alpha:]]{3}', '***', 1, 3) AS result3
  FROM dual

 

 

REGEXP_REPLACE 함수에서 5번째 인자는 패턴 매칭된 횟수를 의미하며, 해당 매칭 횟수의 문자열만 치환한다.

 

아래의 링크를 참고하면 오라클 정규식에 대해 조금 더 쉽게 이해할 수 있을 것이다.

[Oracle] 정규식 사용법 쉽게 설명 (REGEXP)

[Oracle] 오라클 REGEXP_SUBSTR 함수 사용법

[Oracle] 오라클 REGEXP_LIKE 함수 사용법