오라클 regexp_replace 여러개 - olakeul regexp_replace yeoleogae

Question

오라클에서 WHERE 조건에 2개 이상의 값을 넣는 경우가 있다.

목차 Show

TRANSLATE Function
매개변수

이 경우 OR 연산자를 써서 여러 조건을 추가할 수도 있지만,

SELECT * FROM EMPLOYEES WHERE LAST_NAME = 'King' OR LAST_NAME = 'Austin';

OR 연산의 반복을 피하기 위해 IN 연산자를 사용한다.

SELECT * FROM EMPLOYEES WHERE LAST_NAME IN ('King', 'Austin');

조건이 명확할 경우(EX) 이름이 '홍길동', '김영희' 인 데이터 찾기)에는 위의 방법으로 사용이 가능하지만, 특정 문자가 들어간 모든 데이터를 찾고자 할 경우(EX) 이름에 '홍'이 들어간 데이터 찾기)에는 이야기가 달라진다.

우선, 기본적으로 LIKE 연산자를 활용하여야 한다.

SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE '%ins%';

LIKE 연산자 역시 OR 를 반복하여 사용할 수 있다. 하지만, LIKE 연산자는 IN 연산자와 함께 사용할 수 없어, 다음과 같이 사용할 경우 구문 오류를 뱉어낸다.

이를 해결하기 위해 다중 LIKE 연산에는 REGEXP_LIKE(컬럼명, 정규식)을 사용한다.

파이프( | ) 를 이용해 단어를 구분하는데, 일반적으로 단어만 나열할 경우 LIKE '%ins%' 와 같은 효과를 얻을 수 있다.

SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME, 'ins|Mar');

정규표현식을 사용하여 더 효율적인 검색도 가능하다. REGEXP_LIKE 외에도 정규표현식을 사용하는 연산자는 아래와 같다.

REGEXP_LIKE

REGEXP_INSTR

REGEXP_SUBSTR

REGEXP_REPLACE

REGEXP_COUNT : 특정 문자의 개수를 세는 함수

정규표현식 정리

1) '^pattern' : Pattern으로 시작하는 line 출력

SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME, '^A|^B') order by LAST_NAME asc;
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME, '^a|^b', 'i') order by LAST_NAME asc; -- 대소문자 구분 없애기 위해 'i' 추가

2) 'pattern$' : Pattern으로 끝나는 line 출력

SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME, 'a$|s$');

3) 'p....n' : p로 시작하여 n으로 끝나는 line

SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME, 'M...s'); -- . = 1 character

4) '[a-z]*' : 모든 이라는 뜻. 글자수가 0일 수도 있음

5) '[Pp]attern' : Pattern에 해당하는 한 문자

6) '[^a-m]attern' : Pattern에 해당하지 않는 한 문자

References:

https://muyu.tistory.com/entry/Oracle-정규-표현식-정리-regexp

TRANSLATE Function

TRANSLATE(string, from_string, to_string)

TRANSLATE 함수는 특정 문자를 지정된 문자로 치환된 문자열을 반환합니다.

REPLACE 함수와는 다르게 단일 문자를 치환합니다.

설명하기 어려운 함수이므로 바로 샘플 쿼리와 그림으로 설명합니다.

샘플 쿼리

SELECT TRANSLATE('ORACLE', 'OAE', '12') AS TRANS_RESULT
     , LENGTH(TRANSLATE('ORACLE', 'OAE', '12')) AS TRANS_LEN
FROM  DUAL;

치환 과정

원본 문자열의 치환

쿼리 결과

문자 'E'는 공백으로 치환이 되었기 때문에 치환된 문자열의 길이는 5입니다.

매개변수

string

- 기준이 되는 원본 문자열입니다.

from_string

- 검색할 문자열입니다.

to_string

- 치환할 문자열입니다.

특징

첫 번째, 두 번째, 세 번째 매개변수에서 하나라도 NULL 또는 빈 값('')인 경우 TRANSLATE함수는 NULL을 반환합니다.
CLOB 타입에 대해서는 치환을 지원하지 않지만, 문자열로 타입을 변환하면 사용 가능합니다.

EX 1) 첫 번째 매개변수가 NULL 및 공백

SELECT NVL(TRANSLATE('', 'ORA', '123'), 'EMPTY') AS EMPTY_RESULT
     , NVL(TRANSLATE('', 'ORA', '123'), 'NULL') AS NULL_RESULT
FROM  DUAL;

EX 2) 단일 문자 치환

SELECT TRANSLATE('ORACLE', 'O', 'A') AS TRANS_RESULT
FROM  DUAL;

EX 3) 여러 문자 치환

SELECT TRANSLATE('ORACLE', 'OAL', '123') AS TRANS_RESULT
FROM  DUAL;

EX 4) 문자 제거

SELECT TRANSLATE('ORACLE', 'ORA', 'O') AS TRANS_RESULT
FROM  DUAL;

문자 'R'과 'A'의 치환할 문자를 지정하지 않았으므로 제거되었습니다.

EX 5) 띄어쓰기 치환

SELECT TRANSLATE('O R A C L E', ' ', '_') AS TRANS_RESULT
FROM  DUAL;

띄어쓰기를 치환하기 위해서는 두 번째 매개변수에 띄어쓰기(' ')를 명시해줘야 합니다.

공백('')과 띄어쓰기(' ')는 다릅니다.

EX 6) 동일한 문자 여러 번 치환

SELECT TRANSLATE('ORACLE', 'OO', 'AB') AS TRANS_RESULT
FROM  DUAL;

EX 7) 중첩된 REPLACE 함수를 TRANSLATE 함수로 처리

SELECT REPLACE(REPLACE(REPLACE(REPLACE('2*[3+4]/{7-2}','[','('), ']', ')'), '{', '('), '}', ')') AS NEST_REPLACE_RESULT
     , TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()') AS TRAN_RESULT
FROM DUAL;