Numeric or non-numeric values

Database
Enthusiast

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

7 REPLIES
Teradata Employee

Re: Numeric or non-numeric values

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

Enthusiast

Re: Numeric or non-numeric values

Can you try something like this logic?

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

Teradata Employee

Re: Numeric or non-numeric values

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

Enthusiast

Re: Numeric or non-numeric values

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

Senior Apprentice

Re: Numeric or non-numeric values

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]+)$')

Senior Apprentice

Re: Numeric or non-numeric values

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

Enthusiast

Re: Numeric or non-numeric values

Thanks Every one!!

Thanks Dnoeth..!