오라클 정규식 문자만 - olakeul jeong-gyusig munjaman

숫자 혹은 문자로만 된 데이터를 찾아야 하는 일이 생겨 기록해봄

REGEXP_LIKE와 REGEXP_REPLACE 함수를 써서 정규식을 활용한 판별을 진행

(REGEXP는 다양한 종류와 옵션이 있지만 다루지 않음)

1. 정규식

• [0-9] 숫자, [^0-9] 숫자 외의 것
• 그 외의 다양한 정규표현식이 있으니 구글링 ㄱ

2. WHERE절에 쓰는 REGEXP_LIKE을 이용하여 데이터 조회하기

• LIKE문 처럼 해당 정규식에 해당하는 데이터가 한자라도 포함되어 있으면 조회한다.
• NOT을 사용하여 숫자를 포함한 것들을 제외하면 숫자가 하나도 포함되지 않은 데이터를 조회한다.
• 반대로 NOT을 사용하여 숫자가 아닌 것을 포함한 모든 것을 제외하여 숫자로만 이루어진 데이터를 조회한다.

WHERE REGEXP_LIKE(컬럼, '[0-9]') //데이터에 숫자가 하나라도 포함되면 조회
WHERE REGEXP_LIKE(컬럼, '[^0-9]') //데이터에 숫자가 아닌 것이 하나라도 포함되면 조회

WHERE NOT REGEXP_LIKE(컬럼, '[0-9]') //데이터에 숫자가 하나라도 포함되면 제외: 숫자 없는 데이터
WHERE NOT REGEXP_LIKE(컬럼, '[^0-9]') //데이터에 숫자가 아닌 것이 하나라도 포함되면 제외: 숫자

3. SELECT문 컬럼절에 REGEXP_REPLACE를 이용하여 데이터 조회하기

• 해당 정규식에 해당하는 데이터를 제외하고 데이터를 조회한다.
• [0-9]를 넣으면 데이터에서 숫자를 지우고 보여준다.
• [^0-9]를 넣으면 반대로 데이터 속 숫자만 보여준다.

SELECT REGEXP_REPLACE(컬럼, '[0-9]') //문자만 추출
SELECT REGEXP_REPLACE(컬럼, '[^0-9]') //숫자만 추출

4. 끝

• 숫자+문자로 된 데이터가 들어있는 컬럼을 '컬럼', 테이블을 '테이블'로 표기

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 테이블 //숫자만 추출

오라클 데이터베이스에서 전화 번호와 같은 값에서 숫자만 남기고 ), - 등의 다른 문자는 제거하는 방법을 알아 봅니다.

1. 정규식을 사용하는 방법

REGEXP_REPLACE(source_char, pattern) 함수를 사용하는 방법 입니다.

-- 전화번호에서 숫자외의 문자를 제거합니다.
SELECT REGEXP_REPLACE('010-1234-5678', '[^0-9]+') FROM DUAL;
-- 숫자를 자리수로 끊어서 분리합니다.



SELECT REGEXP_REPLACE('01012345678','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})','\1-\2-\3') FROM DUAL;

2. TRANSLATE(expr, from_string, to_string) 함수를 사용하는 방법

SELECT TRANSLATE('010-1234-5678','0'||TRANSLATE('010-1234-5678','x0123456789','x'),'0') FROM DUAL;

TRANSLATE(expr, from_string, to_string) 함수는 expr 문자열에서 from_string내의 첫 번째 문자부터 하나씩 찾아서 발견되면 to_string문자열에서 from_string에서와 같은 위치의 문자로 치환합니다.

to_string의 길이가 짧아서 from_string에서의 위치가 to_string에서 없다면 제거됩니다. expr의 문자가 from_string에 없다면 그대로 가져옵니다.

TRANSLATE('010-1234-5678','x0123456789','x') 을 실행하면 결과는 -- 입니다.

하나씩 알아보면 전화번호에서 'x' 가 있으면 'x' 로 치환됩니다. 0-9까지 문자가 있다면 두 번째 부터는 to_string에 치환할 문자가 없으므로 제거 됩니다. 전화번호의 - 는 from_string에 존재하지 않으므로 그대로 가져옵니다. 

'x'를 사용하는 이유는 to_string에 아무것도 없으면 NULL 이 되어 버리기 때문입니다. 'x'외에 숫자가 아닌 아무 문자나 사용해도 동일한 결과를 얻을 수 있습니다.

이것으로 전화번호에서 제거할 문자를 찾았습니다. 이제 이 문자열을 이용해서 TRANSLATE를 한번 더 사용해서 전화번호에서  숫자를 제외한 문자를 제거합니다.

할매도 코딩한다

Oracle

[Oracle] 숫자, 문자 데이터만 추출하기

꾹꾹이 2022. 5. 2. 15:50

숫자, 문자로만 된 데이터를 찾을 때 사용하는 방법이다.

나는 REGEXP_LIKE 함수를 사용할 것이다.

WHERE 절에서 REGEXP_LIKE(컬럼, '^[[:digit:]]+$')를 사용하면

0부터 9까지 숫자만 들어있는 데이터를 뽑아올 수 있고

문자열 데이터만 뽑아오려면 NOT IN을 사용해서

숫자만 들어있는 컬럼을 제외하면 나머지는 모두 문자열이 되므로 문자열을 뽑아올 수 있다.