Validate data type within SQL

For all those pushing data around, especially dirty data, this one is for you.

Today I was preparing to process data I loaded from a spreadsheet.
A simple filter was required - to ignore the header row, had it been included.

I'm lucky enough to be working on 19c, and I remembered that a reasonably new function should help me out with all many of data loading issues. With a quick scan of my favourite reference manual, I found VALIDATE_CONVERSION.

For example, this gives me 'ORA-01722 invalid number' because of the header row I failed to exclude.

select c.*
from my_data_load c
order by to_number(seq);

But without the to_number, the order returns incorrectly.

SEQ
-----
1
10
12
140
2
Order

Sure, we could say
where seq != 'Order'

But this tool will have more than one use

select c.*
from my_data_load c
where validate_conversion(seq as number) = 1
order by to_number(seq);

SEQ
-----
1
2
10
12
140

Recreate this result using

select * from (
select 'Order' seq from dual
union all select '1' from dual
union all select '2' from dual
union all select '10' from dual
union all select '12' from dual
union all select '140' from dual
)
where validate_conversion(seq as number) = 1
order by to_number(seq)

And see typical return values (0 or 1) for conversion attempts using

select
   validate_conversion('1' as number) num1
  ,validate_conversion('2' as number) num2
  ,validate_conversion('1b' as number)  num_not
  ,validate_conversion('01-01-2001' as date) date1
  ,validate_conversion('30-02-2000' as date, 'dd-mm-yyyy') date2
from dual;  

      NUM1       NUM2    NUM_NOT      DATE1      DATE2
---------- ---------- ---------- ---------- ----------
         1          1          0          0          0

It's one of a few tools I'm using to make data loading life easier, and processing data in sets using SQL, not looping & context switching within PL/SQL.

The kicker, turns out this has been available since 12.2.

It turns out the usage of validate_conversion in PL/SQL will give the compilation warning PLW-06009. And so does the alternative to check if this returns null:
to_date('z-z-2001' default null on conversion error, 'dd-mm-yyyy')

More examples available from
LiveSQL
Tim Hall
Oren Nakdimon
19c Documentation