Data Conversion and Validation(1/3)

게시일: Jun 03, 2020 1:8:49 AM

select to_number(col_1) col_1

from (select 7 col_1 from dual)

;

-------

COL_1

-------

7

-------

select to_number(col_1) col_1

from (select '7' col_1 from dual)

;

-------

COL_1

-------

7

-------

select to_number(col_1) col_1

from (select '6789' col_1 from dual union all

select 'test' col_1 from dual union all -- ORA-01722: invalid number

select '2020-05-12' col_1 from dual union all -- ORA-01722: invalid number

select '2020-12-32' col_1 from dual -- ORA-01722: invalid number

)

;

select to_number(col_1 default -1 on conversion error) col_1 -- 에러가 발생하면 -1 출력

from (select '6789' col_1 from dual union all

select 'test' 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

-------

6789

-1

-1

-1

-------

select to_date(col_1 default '2999-12-31' on conversion error, 'yyyy-mm-dd') col_1 -- 에러가 발생하면 '2999-12-31' 출력

from (select '6789' col_1 from dual union all

select 'test' 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

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

2999-12-31 00:00:00

2999-12-31 00:00:00

2020-05-12 00:00:00

2999-12-31 00:00:00

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