숫자 혹은 문자로만 된 데이터를 찾아야 하는 일이 생겨 기록해봄
REGEXP_LIKE와 REGEXP_REPLACE 함수를 써서 정규식을 활용한 판별을 진행
(REGEXP는 다양한 종류와 옵션이 있지만 다루지 않음)
1. 정규식
- [0-9] 숫자, [^0-9] 숫자 외의 것
- 그 외의 다양한 정규표현식이 있으니 구글링 ㄱ
2. WHERE절에 쓰는 REGEXP_LIKE을 이용하여 데이터 조회하기
- LIKE문 처럼 해당 정규식에 해당하는 데이터가 한자라도 포함되어 있으면 조회한다.
- NOT을 사용하여 숫자를 포함한 것들을 제외하면 숫자가 하나도 포함되지 않은 데이터를 조회한다.
- 반대로 NOT을 사용하여 숫자가 아닌 것을 포함한 모든 것을 제외하여 숫자로만 이루어진 데이터를 조회한다.
3. SELECT문 컬럼절에 REGEXP_REPLACE를 이용하여 데이터 조회하기
- 해당 정규식에 해당하는 데이터를 제외하고 데이터를 조회한다.
- [0-9]를 넣으면 데이터에서 숫자를 지우고 보여준다.
- [^0-9]를 넣으면 반대로 데이터 속 숫자만 보여준다.
4. 끝
- 숫자+문자로 된 데이터가 들어있는 컬럼을 '컬럼', 테이블을 '테이블'로 표기
오라클 데이터베이스에서 전화 번호와 같은 값에서 숫자만 남기고 ), - 등의 다른 문자는 제거하는 방법을 알아 봅니다.
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 함수를 사용할 것이다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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 TB_1 WHERE COL1 NOT IN ( SELECT COL1 FROM TB_1 WHERE REGEXP_LIKE(COL1, '^[[:digit:]]+$') ) ; | cs |
WHERE 절에서 REGEXP_LIKE(컬럼, '^[[:digit:]]+$')를 사용하면
0부터 9까지 숫자만 들어있는 데이터를 뽑아올 수 있고
문자열 데이터만 뽑아오려면 NOT IN을 사용해서
숫자만 들어있는 컬럼을 제외하면 나머지는 모두 문자열이 되므로 문자열을 뽑아올 수 있다.