Teradata Built-In Functions: TO_NUMBER, TRUNC, CEILING, FLOOR, LEAST, GREATEST, ROUND
TO_NUMBER
I have often needed a function to verify whether a character column contains a numeric value. Typically, I have resorted to using a workaround, such as:
CASE WHEN UPPER('12.77') = LOWER('12.77') THEN 'IS_NUMERIC' ELSE 'IS_NOT_NUMERIC' END
Naturally, this workaround is limited and cannot manage certain situations, such as consecutive special characters.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
With the introduction of the TO_NUMBER function in Teradata 14.00, it became possible to verify whether a given string is numeric.
CASE WHEN TO_NUMBER('12.77') IS NOT NULL THEN 'IS_NUMERIC' ELSE 'IS_NOT_NUMERIC' END
Although there has always been an IS_NUMERIC function that users could define, I frequently encounter issues at client sites where execution permissions for these functions are absent. This problem is not present with the built-in functions.
TRUNC
TRUNC will truncate a numeric value (such as decimal(n,m)):
Trunc all decimal places: SELECT TRUNC(12.67) 12.00
Get the next issue by email.
Trunc after the first 2 decimal places: SELECT TRUNC(12.1266,2) 12.1200
CEILING
The CEILING function outputs the minimum integer value greater than or equal to the given input.
SELECT CEILING(4.78)
5.00
FLOOR
This function is comparable to CEILING in operation but instead returns the maximum integer that is less than or equal to the provided input value.
SELECT FLOOR(4.78)
4.00
LEAST
The function LEAST retrieves the minimum value from a series of input values.
SELECT LEAST(12.50, 1.00, 2.88)
1.00
GREATEST
The GREATEST function retrieves the maximum value from a set of input values.
SELECT GREATEST(12.50, 1.00, 2.88)
12.50
ROUND
ROUND will round numerical values.
Round with zero decimal places
SELECT ROUND(12.12)
12.00
Round with two decimal places
SELECT ROUND(12.1266,2)
12.13
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.