error handler for CAST FAILED BAD CHARACTER IN FORMAT OF DATA

Database
Enthusiast

error handler for CAST FAILED BAD CHARACTER IN FORMAT OF DATA

Hi all,

I have a scenario where i have to CAST most of the columns and create and create a view.

Now the issue i am having is some CASTS fail because of bad format in data. In this case i have to put all failed values as NULL.

becaus of this i am writing a custom code everywhere based on the context.

So is there any way to handle these errors like a continue handler.

or is there a generic way in which cast fail exception can be handled??

Is there a solution like Iscastvalid(particular cast) where i can get a result else null as below :

CASE WHEN

     cast(something as something ISVALID())

     then cast(something as something)

     else NULL

END;  

Thanks in advance

Subbu.

2 REPLIES
Enthusiast

Re: error handler for CAST FAILED BAD CHARACTER IN FORMAT OF DATA

And i forgot to mention that i cannot use UDF's.

Enthusiast

Re: error handler for CAST FAILED BAD CHARACTER IN FORMAT OF DATA

Bad characters, untranslatable characters, valid , in valid, it depends. If we know the patterns in advance we can filter them out.

I m not pretty sure if translate_chk can help in your case.

example below:

Function

Result

TRANSLATE_CHK(‘abc’ USING UNICODE_TO_LATIN)

0

TRANSLATE_CHK(‘abc’ USING UNICODE_TO_LATIN)


4