[Oracle] REPLACE 함수 사용법 (문자열 치환, 엔터 제거)

오라클에서 특정 문자열을 치환하거나 제거하기 위해서는 REPLACE 함수를 사용하면 된다. 단순 문자열 치환 외에도 엔터값 제거, 전화번호 특수문자 제거 등 다양한 상황에서 사용할 수 있다. 오라클 10g부터 정규식 사용이 가능한 REGEXP_REPLACE 함수가 추가되었다.

 

오라클 REPLACE 함수 사용법

목차 특정문자 치환 특정문자 제거 엔터값, 탭문자 제거 전화번호 구분자 제거 REGEXP_REPLACE 함수 (정규식)

 

특정문자 치환

SELECT REPLACE('Oracle Database', 'Oracle', 'Ora')  AS result1
     , REPLACE('Oracle Database', 'Database', 'DB') AS result2
     , REPLACE('Oracle Database', 'D', 'd')         AS result3
  FROM dual

 

 

함수사용법 : REPLACE("칼럼명 or 문자열", "찾을문자", "치환문자")

 

REPLACE 함수는 특정 문자 또는 특정 문자열을 치환할 때 사용한다.

오라클 REPLACE 함수는 치환할 문자열을 찾을 때 대문자, 소문자를 구분하기 때문에 유의해야 한다.

 

다중 문자열 치환 (중첩 REPLACE)

SELECT REPLACE(REPLACE('Oracle Database', 'Oracle', 'Ora'), 'Database', 'DB')
  FROM dual

 

 

여러 개의 문자열을 치환할 때는 REPLACE 함수를 중첩으로 사용하면 된다.

 

특정문자 제거

SELECT REPLACE('Oracle Database', 'cle')          AS result1
     , REPLACE('Oracle Database', 'Database')     AS result2
     , REPLACE('Oracle Database', 'Database', '') AS result3
  FROM dual

 

 

REPLACE 함수를 사용하여 특정 문자를 제거할 때는 "치환문자" 항목은 생략하고 "찾을문자"만 입력하면 된다.

 

엔터값, 탭문자 제거

엔터값 제거

WITH temp AS (
  SELECT 'Oracle' || CHR(13)||CHR(10) || 'Database' oradb FROM dual
)

SELECT oradb
     , REPLACE(REPLACE(oradb, CHR(13)), CHR(10))
  FROM temp

 

 

윈도우에서 엔터값은 \r\n(CHR(13)+CHR(10)이기 때문에 두 개의 문자를 제거해야 엔터값이 정확히 제거된다. 참고로 리눅스에서 엔터값은 \n(CHR(10)을 사용한다.

 

탭문자 제거

WITH temp AS (
	SELECT 'Oracle' || CHR(9) || 'Database' oradb FROM dual
)

SELECT oradb
     , REPLACE(oradb, CHR(9)) --탭문자 제거
  FROM temp

 

 

문자변환 함수(CHR)를 사용하여 탭 문자도 쉽게 제거할 수 있다. 

 

전화번호 구분자 제거

SELECT REPLACE('010-1234-5678', '-')               --하이픈 제거
     , REPLACE(REPLACE('010 1234-5678', '-'), ' ') --하이픈+공백 제거
  FROM dual

 

 

전화번호에 포함된 특수문자를 제거할 때도 사용할 수 있다. 전화번호의 특수문자를 제거할 때는 TRANSLATE 함수, REGEXP_REPLACE 함수를 사용하는 것이 조금 더 효율적이다.

 

REGEXP_REPLACE 함수 (정규식)

SELECT REGEXP_REPLACE('Oracle Database', 'Or|Da|ba')                  AS result1
     , REGEXP_REPLACE('Oracle Database', 'Or|Da|ba', 'xx')            AS result2
     , REGEXP_REPLACE('Oracle Database', 'or|da|ba', 'xx', 1, 0, 'i') AS result3
  FROM dual

 

 

오라클 10g부터 정규식을 사용할 수 있는 REGEXP_REPLACE 함수가 추가되었으며, 다중 치환 또는 대소문자 구분 없이 문자열을 치환할 수 있다. 이 외에도 정규식 패턴을 통하여 문자열을 찾고 치환할 수 있기 때문에 조금 더 복잡한 상황의 문자열을 치환할 수 있다.

 

[Oracle] TRANSLATE 함수 사용법 (치환 함수)

[Oracle] REGEXP_REPLACE 사용법 (정규식 치환)

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