select * from test;col1----A12323432412345&^&^%^sdjjsgafsg7326473select col1 from test where upper(col1) (cs) = col1 and lower (col1) (cs) = col1O/p-----732647323432412345
More Precise...........select case when upper(col1) (cs) = col1 and lower (col1) (cs) = col1 then col1 else NULL end from testO/P-------?23432412345??7326473
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
And Col1 Not like any ('%(%' ,'%.%' ,'%''%' ,'%,%' ,'%)%' ,'%>%' , '%;%' , .....)
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.
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!
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