How do you check if a field contains all numeric values using Teradata SQL?

Question

I'm looking for a function similar to ISNUMERIC() from T-SQL with Teradata SQL. I'd like a simple method to return a Boolean (or numeric 1/0) if the data contained in a character-type field is all numeric.

For example:

My column contains the following records: '001' and 'ABC'

I'd expect the query to return: 1 (or True) and 0 (or False).

Is there a method similar to ISNUMERIC() natively supported by TD? I found a few options using regex that I could use, but wanted to see if there was a simpler method before going down that path. If such a method doesn't exist, I'm open to suggestions on accomplishing this.

Answer 1

TD15.10 supports a TRYCAST('ABC' AS INT) which returns NULL when the cast fails.

TO_NUMBER('ABC') also returns NULL for bad data.

The are some differences for strings like '' or '.': TRYCAST('' AS INT) returns 0 and TO_NUMBER('') returns NULL.

Same for seperators like -/:, which are ignored by trycastbut not by to_number: TryCast('05-075' AS INT) returns 5075 and TO_NUMBER returns NULL

You can write a CASE based on that and put it in a SQL UDF.

Answer 2

This will not always work. Please check examples below:

SEL TryCast('12-31-2018' AS INT) -- Result: 12312018
SEL TryCast('05-075' AS INT) -- Result: 5075
SEL TryCast('075+' AS INT) -- Result: 75
SEL TryCast('/099' AS INT) -- Result: 99
SEL TryCast('55/55' AS INT) -- Result: 5555

Recommended:

REGEXP_SIMILAR(TRIM(Value), '^[0-9]{14}$') = 1