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

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

Highlighted
Junior Contributor

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

Junior Contributor

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

Enthusiast

Re: Numeric or non-numeric values

Hi Dnoeth,

 

Will you please explain me the pattern you specified in this function?

 

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

I have a situation where I need to list all the char/varchar columns in a table that ever got a value with all zeros in them.  

Junior Contributor

Re: Numeric or non-numeric values

As I wrote this checks for any Integer/Decimal:

 

^                 = start of string
[-+]?             = optional sign
[0-9]+(\.[0-9]*)? = at least one digit followed by an optional period plus additional digits
|                 = or
\.[0-9]+          = a period followed by at least one digit
$                 = end of string

But the result was wrong, regexp_substr extracts that value while regexp_similar returns 1 or 0.

 

 

To check for all zeroes:

 

regexp_similar('000', '^0+$')