fbpx

The Teradata CHAR2HEXINT Function

By Roland Wenzlofsky

June 5, 2014


A recent blog of Roland Wenzlofsky introduced us to the CHAR2HEXINT function to extract coding table numbers for tokens.

If you found this a little off the scope of practical problems you have to deal with on a daily basis, let me tell you that exactly these functions just helped us a lot in solving a real-world problem.

Assume that you receive a string that can contain any mix of characters or numbers. No instance prevents entries of special characters. Your task is to return the number part as a number if it truly is one and a default value otherwise.

We turned a given solution failing on special characters around by doing the desired extraction if and only if every token of the substring of interest is a number.

Numeric tokens have an adjacent set of hexadecimal values. For anything not purely numeric as a whole, we default to -9 as agreed.

Note that shortcuts such as casting the substring ’12 ‘ (one-two-blank) to an integer provide false security here.

Here the SQL that does the job:

SELECT
CASE WHEN CHAR2HEXINT( SUBSTR(‘X1201’ , 2, 1) )  BETWEEN ‘0030’ AND ‘0039’
AND CHAR2HEXINT( SUBSTR(‘X1201’, 3, 1) ) BETWEEN ‘0030’ AND ‘0039’
AND CHAR2HEXINT( SUBSTR(‘X1201’ , 4, 1) ) BETWEEN ‘0030’ AND ‘0039’
THEN SUBSTR(‘X1201’ , 2, 3) ELSE -9 END

Alternative approaches that I worked with in other DWH environments, such as regular expressions in Oracle SQL, do not come so handy in Teradata SQL.

Add this function to your arsenal of bug repellents and make your character operations less of a coding minefield.

__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
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

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

>