Avoid having error when converting String to Integer in DB2

Question

I want to select a column that is defined as VARCHAR, as an INTEGER like this:

SELECT ID, CAST(Col1 as INT) as MyOutput FROM MyTABLE

The problem is when the column has an invalid format (that can't be converted to Integer) I want to set them to NULL. Is there a way to do this in DB2 ?

Answer 1

One way to accomplish this is to write your own conversion function that handles format exceptions, such as

create or replace function to_int_safe (str varchar(20)) 
returns int 
deterministic 
no external action contains sql 
begin 
  declare continue handler for sqlstate '22018' -- on conversion error
    return null; 
  return cast(str as int); 
end

then use it in your query:

SELECT ID, to_int_safe(Col1) as MyOutput FROM MyTABLE