How to Check Integer Type Values in Teradata? Example

Post author:Vithal S
Post last modified:November 20, 2019
Post category:General
Reading time:4 mins read

Data validation is one of the most important task in the data warehouse environment. Data validation includes, integer type check, count check, etc. For instance, check the row count after data migration. In my other article, we have discussed how to identify the decimal type values. In this article, we will see how to check integer type values in Teradata with some example. Integer type check can also be refereed as a integer data validation.

Teradata Integer Type Values Check

Just like many other relational databases, Teradata also provides many options to verify if a string or input is a integer type.

Following are the couple of methods that you can use to validate integer types in Teradata.

Check Integer value using TO_NUMBER Function
Validate Integer Value using REGEXP_SIMILAR Regular Expression

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

Check Integer value using TO_NUMBER Function

The TO_NUMBER function is one of the easiest function that you can use to validate integer type column or expression.

The earlier version of Teradata did not support the TO_NUMBER function. However, starting Teradata 14.00 introduced the TO_NUMBER function.

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

select case 
when TO_NUMBER('123345') is not null then 'True' else 'False' 
end as col1 ;

col1
True

select case 
when TO_NUMBER('aaaa123345') is not null then 'True' else 'False' 
end as col1 ;

col1
False

Use TO_NUMBER in WHERE clause to remove non integer records.

REGEXP_SIMILAR Regular Expression To Check Integer Type

In this method, we will use REGEXP_SIMILAR Regular Expression. This is one of the fastest method to check if a string is integer.

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

select case 
when REGEXP_SIMILAR(TRIM('123345'), '^([0-9]*)+$') = 1 then 'True' else 'False' 
end  as col1;;

col1
True

select case 
when REGEXP_SIMILAR(TRIM('123.12'), '^([0-9]*)+$') = 1 then 'True' else 'False' 
end  as col1;;

col1
False

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