fbpx

Teradata Numeric Functions

By 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

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>