안녕하세요.

테이블 문자형 칼럼값에서 숫자로 입력된 값 중 원하는 숫자 범위만 조회가 가능한지 궁금합니다.

아래는 샘플쿼리입니다.

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')

읽어주셔서 감사합니다.