오라클에서 WHERE 조건에 2개 이상의 값을 넣는 경우가 있다.
이 경우 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 출력
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:
//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;