6

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.

NzGuy
  • 382
  • 3
  • 14
Sevyns
  • 2,992
  • 5
  • 19
  • 23

2 Answers2

9

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.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • This should work perfectly - thanks! I'm new to TD and not sure what version we're on. I don't see trycast as an option, but we do have to_number. I'll just wrap that up in a fn and that. – Sevyns Oct 19 '16 at 15:57
  • @Sevyns: `select * from dbc.dbcinfoV;` shows the actual release. – dnoeth Oct 19 '16 at 16:21
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
user2845337
  • 43
  • 1
  • 4
  • Yep, `trycast` uses the same rules in Teradata as CAST (ignoring seperators in FORMAT), so better use `to_number` – dnoeth Nov 19 '18 at 17:52