FINDING INVALID DATA

Database
Enthusiast

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

Tags (1)
1 REPLY
Enthusiast

Re: FINDING INVALID DATA

SEL 

CASE 

WHEN REGEXP_SIMILAR('2Q','^[0-9]*$','i')=1

THEN CAST('2Q' AS DECIMAL(9,0))

ELSE '2Q'

END

-----In >= TD 14