All Forums Database
ah255012 8 posts Joined 07/11
12 Jan 2012
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

Regards, Aamir Hussain.
CarlosAL 512 posts Joined 04/08
12 Jan 2012

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.

ulrich 816 posts Joined 09/09
12 Jan 2012

Check the http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs

there are some very good UDFs

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ah255012 8 posts Joined 07/11
22 Jan 2012

Thanks CarlosAL and ulrich, i got the solution

Regards,

Aamir Hussain.

ulrich 816 posts Joined 09/09
23 Jan 2012

Hi,

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

xaeru 1 post Joined 11/12
30 Nov 2012

What about ISNUMERIC() ?
 
http://msdn.microsoft.com/en-us/library/ms186272.aspx

mohan.mscss 31 posts Joined 04/11
04 Dec 2012

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

You must sign in to leave a comment.