Countless times I would have needed a function which 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 which can’t handle all kind 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.