오라클 정규식 사용방법 REGEXP함수들

오라클 REGEXP 정규식 사용방법에 대해서 알아보자

오라클 REGEXP (Reqular Expression 의 약어 정규표현식이다.)

각 언어마다 대부분 존재하며 사용방법은 조금씩 다르다.

이전에 자바스크립트 정규표현식을 보고 싶다면 아래 링크로

https://blog.lsj.kr/regexp/ => 자바스크립트 정규표현식 관련글

데이터에 *와 숫자가 혼합되어 들어 있는 문자열 컬럼인데, 숫자만 가져와 곱셈을 하는 로직이 있는 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    -- 끝에 멀로 변환할것인지를 생략가능!
-- 숫자가 아닌 애들을 공백으로 바꿔라 정도가 되겠다.