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

teradata
Sevyns picture Sevyns · Oct 19, 2016 · Viewed 19.6k times · Source

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

dnoeth picture dnoeth · Oct 19, 2016

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.

Related questions

SQL SELECT multi-columns INTO multi-variable

I'm converting SQL from Teradata to SQL Server in Teradata, they have the format SELECT col1, col2 FROM table1 INTO @variable1, @variable2 In SQL Server, I found SET @variable1 = ( SELECT col1 FROM table1 ); That only allows a single column/variable …

Read More
What is ROWS UNBOUNDED PRECEDING used for in Teradata?

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the …

Read More
Find which rows have different values for a given column in Teradata SQL

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 456 Wall 2 2 456 Wall 3 1 789 Right 3 2 100 Left I'm just trying to figure out whether the address for each ID …

Read More