All Forums Database
PKJK07 2 posts Joined 08/09
07 Aug 2009
Ignore Alphanumeric data content

Hi All,I have a dataset which contains 1) numeric only 2) mixture of alphanumeric,special characters,space and numeric in it.I want to only consider the numeric data and ignore the 2 option.ex.1. 123456 -- Valid and accept2. A#234 Intel34 -- Ignore and pass NULLHow can i achieve this without using a CASE expression.ThanksPKJK

gander_ss 74 posts Joined 02/07
07 Aug 2009

select * from test;col1----A12323432412345&^&^%^sdjjsgafsg7326473select col1 from test where upper(col1) (cs) = col1 and lower (col1) (cs) = col1O/p-----732647323432412345

gander_ss 74 posts Joined 02/07
07 Aug 2009

More Precise...........select case when upper(col1) (cs) = col1 and lower (col1) (cs) = col1 then col1 else NULL end from testO/P-------?23432412345??7326473

PKJK07 2 posts Joined 08/09
08 Aug 2009

Thanks Subash,It working fine with all the possibilities butfor ex like a) (1) 4506b (48) 4006c) 22.11.06d) 4700 $e) 600 -> 600f) 50 - 100How do i trap them.ThanksPKJK

Jimm 298 posts Joined 09/07
11 Aug 2009

And Col1 Not like any ('%(%' ,'%.%' ,'%''%' ,'%,%' ,'%)%' ,'%>%' , '%;%' , .....)

gander_ss 74 posts Joined 02/07
11 Aug 2009

Let me know solution provided by jimm is working for you.Also when you are going to have all special character in where clause use backslash to supress the special meaning of the character.

Jimm 298 posts Joined 09/07
12 Aug 2009

Unfortunately, Teradata does not support Unix-like regular expressions, so backlash does not do an escape.If one of the characters you want to search-for/ exclude is a %, you need:And Col1 Not like any ('%(%' ,'%.%' ,'%''%' ,'%,%' ,'%)%' ,'%>%' , '%;%' , '%%%%', .....) You can include a tab character between the percent signs (if you are running from Linux/ Unix) and you want to exclude tab characters.You can do the same on mainframe, but ISPF expands the tab to spaces, so you will have trouble getting it in!

You must sign in to leave a comment.