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
