오라클 REGEXP 정규식 사용방법에 대해서 알아보자
오라클 REGEXP (Reqular Expression 의 약어 정규표현식이다.)
각 언어마다 대부분 존재하며 사용방법은 조금씩 다르다.
이전에 자바스크립트 정규표현식을 보고 싶다면 아래 링크로
https://blog.lsj.kr/regexp/ => 자바스크립트 정규표현식 관련글
함수명 | 비고 |
REGEXP_LIKE | 정규표현식을 이용해서 데이터 검색할때 사용 LIKE랑 유사함 |
REGEXP_REPLACE | 정규식을 사용해서 문자열을 바꾸는 함수 |
REGEXP_SUBSTR | 정규식 사용 문자 추출 |
REGEXP_INSTR | 정규식 사용해서 인덱스 위치 반환 |
REGEXP_COUNT | 정규식에 해당하는 문자열 갯수 반환(오라클 11g버전 이후) |
예제를 보면서 습득하는게 좋을테니 바로 예제로 들어 간다.~~
데이터에 *와 숫자가 혼합되어 들어 있는 문자열 컬럼인데, 숫자만 가져와 곱셈을 하는 로직이 있는 SQL이 있었다.
SELECT TO_NUMBER(A.COL) * 10 AS VAL
FROM TB_TEST A
WHERE A.COL <> '*'
;
SQL Error [ERRDT]: JDBC-5074:Given string does not represent a number in proper format.
헌데 문자열 데이터에 숫자와 * 이외에도 1Z 등 숫자가 아닌 놈들이 들어 오게 되었다.
WHERE 절을 REGEXP을 이용해서 숫자만 추출하면 sql오류를 잡을 수 있다.
WHERE REGEXP_LIKE(A.COL, '[0-9]')
하지만 오류는 여전하다. 이유는 숫자가 포함된 데이터는 모두 가져오기에 1Z, 77A 머 요런것들이 걸러지지 않아서이다.
WHERE REGEXP_LIKE(A.COL, '[0-9]') IS NULL
인데, 티베로에선
SQL Error [42000]: JDBC-8007:Missing boolean expression.
at line 5, column 42 of null:
오류가 난다. ㅡㅡ^
오라클에서는 반환되는 게 TRUE, FALSE, NULL 인가 보다. 하지만 티베로는 아니니 아래와 같이 NOT 연산자를 적절히 섞어 사용하면된다.
SELECT 컬럼 FROM 테이블 WHERE REGEXP_LIKE(컬럼, '[^0-9]') //숫자 외의 것이 포함된 것
SELECT 컬럼 FROM 테이블 WHERE NOT REGEXP_LIKE(컬럼, '[^0-9]') //숫자외의 것이 포함된 것이 아닌 것 → Only 숫자
SELECT 컬럼 FROM 테이블 WHERE REGEXP_LIKE(컬럼, '[0-9]') //숫자가 포함된 것
SELECT 컬럼 FROM 테이블 WHERE NOT REGEXP_LIKE(컬럼, '[0-9]') //숫자가 포함된 것이 아닌 것 → Only 문자
SELECT REGEXP_REPLACE(컬럼, '[0-9]') FROM 테이블 //문자만 추출
SELECT REGEXP_REPLACE(컬럼, '[^0-9]') FROM 테이블 //숫자만 추출
숫자만 있는 놈들만 쓸려면
SELECT 컬럼 FROM 테이블 WHERE NOT REGEXP_LIKE(컬럼, '[^0-9]'); -- 요놈인것이다
요런식으로 문자열에 숫자만 들어있는 ROW만 숫자로 변환해서 사용할 수 있다.
13Z, Z25Z, AB13G3 요런 데이터가 있다면 숫자로 걍 바꿔서 써도 되는거
SELECT REGEXP_REPLACE('AB13G3','[^0-9]') FROM DUAL
SELECT REGEXP_REPLACE('AB13G3','[^0-9]','') FROM DUAL -- 끝에 멀로 변환할것인지를 생략가능!
-- 숫자가 아닌 애들을 공백으로 바꿔라 정도가 되겠다.