Ignore Alphanumeric data content

Database
Fan

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 accept
2. A#234 Intel34 -- Ignore and pass NULL

How can i achieve this without using a CASE expression.

Thanks
PKJK

6 REPLIES
Enthusiast

Re: Ignore Alphanumeric data content

select * from test;

col1
----
A123
234324
12345
&^&^%^sdjj
sgafsg
7326473

select col1 from test where upper(col1) (cs) = col1 and lower (col1) (cs) = col1

O/p
-----
7326473
234324
12345
Enthusiast

Re: Ignore Alphanumeric data content

More Precise...........

select
case when upper(col1) (cs) = col1 and lower (col1) (cs) = col1 then col1 else NULL end from test

O/P
-------
?
234324
12345
?
?
7326473
Fan

Re: Ignore Alphanumeric data content

Thanks Subash,

It working fine with all the possibilities but

for ex like

a) (1) 4506
b (48) 4006
c) 22.11.06
d) 4700 $
e) 600 -> 600
f) 50 - 100

How do i trap them.

Thanks
PKJK
Enthusiast

Re: Ignore Alphanumeric data content

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

Re: Ignore Alphanumeric data content

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.
Enthusiast

Re: Ignore Alphanumeric data content

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!