Teradata isnumeric Function Alternatives and Examples

Teradata is one of the widely used MPP databases. It can be used to combine many data sources. When you work with heterogeneous data set, you may have to get rid of many unwanted characters such as $ in your price column. If your application requirement is of numeric type, you may get requirement to filter out non-numeric values. In this article, we will check Teradata isnumeric function alternatives with some examples. We will also see how to check if string is numeric with an example.

Teradata isnumeric Function

The relational databases such as SQL-Server supports isnumeric functions. But, as of now Teradata does not support is numeric function. You have to use an alternate methods to check if a string or value is numeric type in Teratadat.

You can use any of the following methods as an isnumeric function alternative.

TO_NUMBER Function
REGEXP_SIMILAR Regular Expression

Now, let us verify the above approaches with an example.

Teradata TO_NUMBER Function

The earlier version of Teradata did not support the TO_NUMBER function. However, starting Teradata 14.00 introduced the TO_NUMBER function, you can use this function to check if a string is numeric.

For example, following SQL query demonstrates the usage of TO_NUMBER function.

select case 
when TO_NUMBER('123.345') is not null then 'True' else 'False' 
end;
	
<CASE  expression>
True

select case 
when TO_NUMBER('123.aas345') is not null then 'True' else 'False' 
end;
	
<CASE  expression>
False

You can filter non-numeric values by using TO_NUMBER function in WHERE clause.

REGEXP_SIMILAR Regular Expression

The second method, is to use REGEXP_SIMILAR Regular Expression. If you know about regular expression, then this is the method for you.

For example, following regular expression checks if string is numberic.

select case 
when REGEXP_SIMILAR(TRIM('123.345'), '^(([0-9]*)|(([0-9]*)\.([0-9]*)))+$') = 1 then 'True' else 'False' 
end;

<CASE  expression>
True

select case 
when REGEXP_SIMILAR(TRIM('123.asdsa345'), '^(([0-9]*)|(([0-9]*)\.([0-9]*)))+$') = 1 then 'True' else 'False' 
end;

<CASE  expression>
False

Just like TO_NUMBER function, you can use the REGEXP_SIMILAR function in the WHERE clause to filter out non-numeric values.

Alternate Method

You can also check if input is string by using Teradata UPPER/LOWER string function.

For example, consider below example that to identify the string and numeric values.

select CASE 
WHEN UPPER('12.77') = LOWER('12.77') THEN 'True' ELSE 'FALSE' 
END;

<CASE  expression>
True

select CASE 
WHEN UPPER('12.qq77') = LOWER('12.77') THEN 'True' ELSE 'FALSE' 
END;

<CASE  expression>
False