TIBERO 숫자 체크 - TIBERO susja chekeu

[SQL] 숫자만 또는 숫자외(글자포함) 데이터 찾기 조건 본문

Stella_NY 2017. 5. 1. 15:01

숫자가 포함된 데이터 (varchar) 찾기 조건

WHERE TRIM(TRANSLATE(비교할 컬럼,'0123456789',' ')) IS NOT NULL

숫자만 찾기 조건

WHERE TRIM(TRANSLATE(비교할 컬럼,'0123456789',' ')) IS NULL

반응형

ORACLE, TIBERO REGEXP_SUBSTR 사용법

1. DB

DB에 해당 데이터가 있다고 가정하고 

REGEXP_SUBSTR(CODE_PATH,'[^/]+',1,1) AS CODE1  부분을 살펴보면 아래와 같다.

2. SQL

SELECT
			REGEXP_SUBSTR(CODE_PATH,'[^/]+',1,1) AS CODE1
FROM CODE;

//결과 : 21


SELECT
			REGEXP_SUBSTR(CODE_PATH,'[^/]+',1,2) AS CODE2
FROM CODE;


//결과 : 49


SELECT
			REGEXP_SUBSTR(CODE_PATH,'[^/]+',1,3) AS CODE2
FROM CODE;


//결과 : 55

반응형

공유하기

게시글 관리

저작자표시 비영리 변경금지

'웹프로그래밍 > Oracle or Sql' 카테고리의 다른 글

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

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

▶ REGEXP_INSTR 함수 사용하기

. Oracle 10g 이상 버전부터 사용 가능하다.


WITH TEST_TABLE AS
(
    SELECT '신라면' PRODUCT, '650'  PRICE FROM DUAL UNION ALL
    SELECT '너구리' PRODUCT, '700'  PRICE FROM DUAL UNION ALL
    SELECT '진라면' PRODUCT, '$460' PRICE FROM DUAL
)
SELECT
    PRODUCT
   ,TO_NUMBER(PRICE) PRICE
FROM  TEST_TABLE
WHERE REGEXP_INSTR(PRICE, '[^0-9]') = 0; -- 정수만 체크하는 경우

▶ REGEXP_INSTR 함수를 사용해 부호(+, -), 소수점까지 체크하는 경우

WITH TEST_TABLE AS
(
    SELECT '신라면' PRODUCT, '650'  PRICE FROM DUAL UNION ALL
    SELECT '너구리' PRODUCT, '700'  PRICE FROM DUAL UNION ALL
    SELECT '진라면' PRODUCT, '$460' PRICE FROM DUAL
)
SELECT
    PRODUCT
   ,TO_NUMBER(PRICE) PRICE
FROM  TEST_TABLE
WHERE REGEXP_INSTR(PRICE, '^[+-]?\d*(\.?\d*)$') = 1;

▶ TRANSLATE 함수 사용하기

. Oracle 8i 이상 버전에서 사용 가능하다.


WITH TEST_TABLE AS
(
    SELECT '신라면' PRODUCT, '650'  PRICE FROM DUAL UNION ALL
    SELECT '너구리' PRODUCT, '700'  PRICE FROM DUAL UNION ALL
    SELECT '진라면' PRODUCT, '$460' PRICE FROM DUAL
)
SELECT
    PRODUCT
   ,TO_NUMBER(PRICE) PRICE
FROM  TEST_TABLE
WHERE TRANSLATE(PRICE, 'A1234567890', 'A') IS NULL;

▶ 사용자 정의 함수 사용하기 (IS_NUMBER)

CREATE OR REPLACE FUNCTION IS_NUMBER(P_SOURCE VARCHAR2) RETURN NUMBER
IS
    V_RESULT NUMBER;
BEGIN
    IF P_SOURCE IS NULL OR LENGTH(TRIM(P_SOURCE)) = 0 THEN
        RETURN 0;
    END IF;

    V_RESULT := TO_NUMBER(P_SOURCE);

    RETURN 1;

EXCEPTION WHEN OTHERS THEN

    RETURN 0;
END;

WITH TEST_TABLE AS
(
    SELECT '신라면' PRODUCT, '650'  PRICE FROM DUAL UNION ALL
    SELECT '너구리' PRODUCT, '700'  PRICE FROM DUAL UNION ALL
    SELECT '진라면' PRODUCT, '$460' PRICE FROM DUAL
)
SELECT
    PRODUCT
   ,TO_NUMBER(PRICE) PRICE
FROM  TEST_TABLE
WHERE IS_NUMBER(PRICE) = 1;