Check row is Numeric or not

Database
Teradata Employee

Check row is Numeric or not

Need help to find that a row is numeric or not.

i am trying this

SEL trim('123#4567')

,CASE

WHEN (

POSITION(' ' IN TRIM('123#4567')) > 0 OR POSITION('!' IN TRIM('123#4567')) > 0 OR POSITION('@' IN TRIM('123#4567')) > 0 OR

POSITION('#' IN TRIM('123#4567')) > 0 OR POSITION('$' IN TRIM('123#4567')) > 0 OR POSITION('%' IN TRIM('123#4567')) > 0 OR

POSITION('^' IN TRIM('123#4567')) > 0 OR POSITION('&' IN TRIM('123#4567')) > 0 OR POSITION('*' IN TRIM('123#4567')) > 0 OR

POSITION('(' IN TRIM('123#4567')) > 0 OR POSITION(')' IN TRIM('123#4567')) > 0 OR POSITION('-' IN TRIM('123#4567')) > 0 OR

POSITION('_' IN TRIM('123#4567')) > 0 OR POSITION('=' IN TRIM('123#4567')) > 0 OR POSITION('+' IN TRIM('123#4567')) > 0 OR

POSITION('{' IN TRIM('123#4567')) > 0 OR POSITION('}' IN TRIM('123#4567')) > 0 OR POSITION('[' IN TRIM('123#4567')) > 0 OR

POSITION(']' IN TRIM('123#4567')) > 0 OR POSITION(';' IN TRIM('123#4567')) > 0 OR POSITION(':' IN TRIM('123#4567')) > 0 OR

POSITION('"' IN TRIM('123#4567')) > 0 OR POSITION('''' IN TRIM('123#4567')) > 0 OR POSITION('?' IN TRIM('123#4567')) > 0 OR

POSITION('/' IN TRIM('123#4567')) > 0 OR POSITION('>' IN TRIM('123#4567')) > 0 OR POSITION('.' IN TRIM('123#4567')) > 0 OR

POSITION('<' IN TRIM('123#4567')) > 0 OR POSITION('~' IN TRIM('123#4567')) > 0 OR POSITION(',' IN TRIM('123#4567')) > 0 )

OR (UPPER(TRIM('123#4567')) (CASESPECIFIC) <> LOWER(TRIM('123#4567')) (CASESPECIFIC))

THEN 'Non-Numeric'

ELSE 'Numeric' End;

Need to find the best solution, Kindly share if you know the best solution.

Regards,

Aamir Hussain

6 REPLIES
Junior Supporter

Re: Check row is Numeric or not

Hi.

I provided a solution for a colleague who needed to check whether a string was 'only numbers' or not.

It's explained here:

http://carlosal.wordpress.com/2009/12/02/only-numbers/

HTH.

Cheers.

Carlos.

Supporter

Re: Check row is Numeric or not

Teradata Employee

Re: Check row is Numeric or not

Thanks CarlosAL and ulrich, i got the solution

Supporter

Re: Check row is Numeric or not

Hi,

we shared our thought and at least I would be interessted how you solved your problem!

N/A

Re: Check row is Numeric or not

Enthusiast

Re: Check row is Numeric or not

try with reursive, hope it can help you

with recursive r2(id, st, L, c, rstr) as

(

sel id,str as st, char(st) as L, 1 as c, trim(case when position(substr(st,1,1) in 'abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+-=<>,.?:;"'') > 0 then 1 else 0 as rstr from revString

union all

sel r2.id,r2.st, char(st) as L, c+1 as c, rstr || trim(case when position(substr(r2.st,c+1,1) in 'abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+-=<>,.?:;"'') > 0 then 0 else 1 as rstr from  r2

where rstr = 0 and L <> c

)sel case when rstr = 0 then 'Numeric' else 'Non-numeric' from r2 order by id,4 desc ;

@zaeru: Isnumeric is SQL version, Not TD function..