How to handle string or blank in the Integer column?

Today I was facing a challenge while loading the big integer to the dimensional table. Unfortunately the source table contains a string (varchar) in it and which broke my Batch loading process.

After all research in internet, I found the below fix solved my case. I have used text_to_int_alt case when then else end and convert API's.


select text_to_int_alt(case
                           when regexp_replace(convert(varchar, 'UNKNOWN'), '[^0-9]', '') <> '' then
                               regexp_replace(convert(varchar, 'UNKNOWN'), '[^0-9]', '')
                           else '-1'
    end);
-- output -1

select (case
            when regexp_replace(trim(convert(varchar, '4634xcvs235!"£"$')), '[^0-9]', '') <> '' then
                regexp_replace(trim(convert(varchar, '4634xcvs235!"£"$')), '[^0-9]', '')
    end);
-- output 4634235

select (case
            when regexp_replace(convert(varchar, 'UNKNOWN'), '[^0-9]', '') <> '' then
                regexp_replace(convert(varchar, 'UNKNOWN'), '[^0-9]', '') else '-1'
    end);
-- output null