casting char to int issues

Database

casting char to int issues

I am trying to cast a char field to an int so that I can compare it with another int, however there is of course some junk data in the char field which is causing an error (2620 the format or data contains a bad character) when I try and cast it. The error breaks the query and I am wondering if there is any function or method in which I could test if the char field contains only integers and only cast it if it does, otherwise drop that record. The good values range from 0-100 however some bad examples are '03D' or 'XXX'.

One thought I had was if there was a function similar to trim in which you could specify the characters to remove, and if you remove all the digits and it is equal to '' (empty string) then clearly it contained only integers. However I am not very familiar with all the teradata specific functions so I do not think that exists.

Thanks for any suggestions

PS also if there are any links to good documentation on teradata functions like TRIM etc that would be very useful
1 REPLY
Enthusiast

Re: casting char to int issues

There is no specific function to do what you are asking.But, you can write a udf to do what you want. other approach is to use check using LIKE operator with wildcard.ofcourse this is very tedious to implement and I wouls also not recommend to do it. But, if nothing works then you can go ahead doing the check for eg:- LIKE ('a%','%a'......)

The best approach is to write a UDF