# Teradata Built-In Functions: TO_NUMBER, TRUNC, CEILING, FLOOR, LEAST, GREATEST, ROUND

Roland Wenzlofsky

April 27, 2023

## 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 LEAST(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 three decimal places
SELECT ROUND(12.1266,2)
12.13