All Forums Database
subbu.balu93 13 posts Joined 09/14
17 Sep 2014
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.

subbu.balu93 13 posts Joined 09/14
17 Sep 2014

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

Raja_KT 1246 posts Joined 07/09
17 Sep 2014

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

 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.