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.

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

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