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. :)
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
Hope it helps !
Can you try something like this logic?
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
Yes, it won't. nothing compares to udfs :). This is just a quick one, in case that col does not have whitespace, special characters.
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'
I just remembered the Ebay UDFs, there's a eIsDecimal UDF which should be exactly what you need:
Will you please explain me the pattern you specified in this function?
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.
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: