TO_NUMBER
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 all decimal places: SELECT TRUNC(12.67) 12.00
Trunc after the first 2 decimal places: SELECT TRUNC(12.1266,2) 12.1200
CEILING
SELECT CEILING(4.78)
5.00
FLOOR
SELECT FLOOR(4.78)
4.00
LEAST
SELECT LEAST(12.50, 1.00, 2.88)
1.00
GREATEST
SELECT LEAST(12.50, 1.00, 2.88)
12.50
ROUND
Round with zero decimal places
SELECT ROUND(12.12)
12.00
Round with three decimal places
SELECT ROUND(12.1266,2)
12.13