Roland Wenzlofsky

December 31, 2016

## TO_NUMBER

Countless times, I would have needed a function that allows me to check if a character column represents a numeric value. Usually, I used a workaround such as:

CASE WHEN UPPER(‘12.77’) = LOWER(‘12.77’) THEN ‘IS_NUMERIC’ ELSE ‘IS_NOT_NUMERIC’ END

Of course, it’s only a workaround that can’t handle all kinds of situations, such as strings consisting only of special characters.

When Teradata 14.00 introduced the TO_NUMBER function, we finally became the possibility to check ifÂ  a string is numeric:

CASE WHEN TO_NUMBER(‘12.77’) IS NOT NULL THEN ‘IS_NUMERIC’ ELSE ‘IS_NOT_NUMERIC’ END

You may say, “but there was always a user-defined function IS_NUMERIC available, and you are right. But the problem I often experience at client sites is that execution permissions on these user-defined functions are missing. We don’t have this problem 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

CEILING returns the smallest integer value not less than the input value

SELECT CEILING(4.78)
5.00

## FLOOR

Similar to CEILING, but returns the largest integer equal or less than the input value

SELECT FLOOR(4.78)
4.00

## LEAST

LEAST returns the smallest value from a list of input values

SELECT LEAST(12.50, 1.00, 2.88)
1.00

## GREATEST

GREATEST returns the highest value from a list of input values

SELECT LEAST(12.50, 1.00, 2.88)
12.50

## ROUND

ROUND will round the numeric value.

Round with zero decimal places
SELECT ROUND(12.12)
12.00

Round with three decimal places
SELECT ROUND(12.1266,2)
12.13