Data Conversion and Validation(3/3)

게시일: Jun 03, 2020 1:14:58 AM

- 형변환 가능여부 판단

- validate_conversion (1:형변환 가능, 0:형변환 불가능)

- varchar 등은 안되는 듯.

select validate_conversion(col_1 as date) col_1

, validate_conversion(col_1 as date, 'yyyy-mm-dd') col_2

, validate_conversion(col_1 as date, 'yyyy-mm') col_3

, validate_conversion(col_1 as timestamp, 'yyyy-mm-dd') col_4

, validate_conversion(col_1 as number) col_5

from (select '6789' col_1 from dual union all

select 'test' col_1 from dual union all

select '2020-05' col_1 from dual union all

select '2020-05-12' col_1 from dual union all

select '2020-12-32' col_1 from dual

)

;

--------------------------------------

COL_1 COL_2 COL_3 COL_4 COL_5

--------------------------------------

0 0 0 0 1

0 0 0 0 0

0 0 1 0 0

1 1 0 1 0

0 0 0 0 0

--------------------------------------

select col_1

from (select '6789' col_1 from dual union all

select 'test' col_1 from dual union all

select '2020-05' col_1 from dual union all

select '2020-05-12' col_1 from dual union all

select '2020-12-32' col_1 from dual

)

where validate_conversion(col_1 as date, 'yyyy-mm-dd') = 1

;

------------------------------

COL_1

------------------------------

2020-05-12

------------------------------