안녕하세요.
테이블 문자형 칼럼값에서 숫자로 입력된 값 중 원하는 숫자 범위만 조회가 가능한지 궁금합니다.
아래는 샘플쿼리입니다.
1. 숫자형태만 뽑는 쿼리
SELECT
NO,
ATWRT
FROM
(
SELECT 1 AS NO, '0.01' AS ATWRT FROM dual
UNION
SELECT 2 AS NO, '2.5' AS ATWRT FROM dual
UNION
SELECT 3 AS NO, '1' AS ATWRT FROM dual
UNION
SELECT 4 AS NO, '14.99' AS ATWRT FROM dual
UNION
SELECT 5 AS NO, '3' AS ATWRT FROM dual
UNION
SELECT 6 AS NO, 'ABC' AS ATWRT FROM dual
)
WHERE
REGEXP_LIKE(TRIM(ATWRT), '[[:digit:]]')
2. 위 쿼리에서 2보다 크고, 5보다 작은 값을 조회하려고 하면
'ORA-01722 수치가 부적합합니다.' 오류가 발생합니다.
SELECT
NO,
ATWRT
FROM
(
SELECT 1 AS NO, '0.01' AS ATWRT FROM dual
UNION
SELECT 2 AS NO, '2.5' AS ATWRT FROM dual
UNION
SELECT 3 AS NO, '1' AS ATWRT FROM dual
UNION
SELECT 4 AS NO, '14.99' AS ATWRT FROM dual
UNION
SELECT 5 AS NO, '3' AS ATWRT FROM dual
UNION
SELECT 6 AS NO, 'ABC' AS ATWRT FROM dual
)
WHERE
REGEXP_LIKE(TRIM(ATWRT), '[[:digit:]]')
AND TO_NUMBER(ATWRT) BETWEEN TO_NUMBER('2') and TO_NUMBER('5')
읽어주셔서 감사합니다.
|