All Forums Database
indexpert 5 posts Joined 01/14
12 May 2014
Numeric or non-numeric values

Hi,
I am looking for any inbuilt function in teradata like is_number() .
Currently, I have to split a source column in to two different columns, if data is numeric then it should populate COL1 and if not then COL2.
I tried some google, but didnt get the solution because in my source column, I have signed decimal values as well.
 
looking for solution.
Thanks in advance. :)

FarhanArif 7 posts Joined 05/12
13 May 2014

Hi,
There are no inbuilt functions in Teradata that have this functionality.
The best option would be a UDF. Either you can write one yourself or you can try the string UDFs from
http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs
Hope it helps !
Regards,
Farhan

Raja_KT 1246 posts Joined 07/09
13 May 2014

Can you try something like this logic?

select your_col  from your_table where upper(your_col ) (casespecific) = lower(your_col  )  (casespecific)

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

FarhanArif 7 posts Joined 05/12
13 May 2014

Hi Raja,
This logic won't check for spaces or special characters. A string like  '#(* 231 $&!' will also return true here.
Special characters and white spaces are usally handled in UDFs
Farhan

 

Raja_KT 1246 posts Joined 07/09
13 May 2014

Yes, it won't. nothing compares to udfs :). This is just a quick one, in  case that col does not have whitespace, special characters.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
13 May 2014

What's your TD release?
In TD14 there are regular expressions, e.g. this will return 1 for any kind of decimal like ' 24', '-23.66', '+2.98'

regexp_instr(trim(col), '^[-+]?([0-9]+(\.[0-9]*)?|\.[0-9]+)$')

Dieter

dnoeth 4628 posts Joined 11/04
14 May 2014

I just remembered the Ebay UDFs, there's a eIsDecimal UDF which should be exactly what you need:
 http://developer.teradata.com/blog/madmac/2010/03/a-few-basic-scalar-string-udfs

Dieter

indexpert 5 posts Joined 01/14
12 Dec 2014

Thanks Every one!!
Thanks Dnoeth..!

You must sign in to leave a comment.