All Forums Database
kartaj 10 posts Joined 10/15
24 Apr 2016
FINDING INVALID DATA

Hi team,
I am trying to convert the data from varchar to decimal... But getting failed... got to know that there are few invalid data... But i have checked for 21 lakhs records.... 
can you please tell me any way to get the invalid data in a column...
ex:
15
2q
423
 
here invalid data is 2q ... i want to find only invalid data... can you please tell me to write a regexp to find data other then numbers or decimal
 
 

yuvaevergreen 93 posts Joined 07/09
24 Apr 2016

SEL 
CASE 
WHEN REGEXP_SIMILAR('2Q','^[0-9]*$','i')=1
THEN CAST('2Q' AS DECIMAL(9,0))
ELSE '2Q'
END
-----In >= TD 14

You must sign in to leave a comment.