June 5


The Teradata CHAR2HEXINT Function

By Roland Wenzlofsky

June 5, 2014

CHAR2HEXINT, character conversion, sql

A recent blog of Roland Wenzlofsky introduced to us 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 this 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. There is no instance that 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 a false security here.

Here the SQL that does the job:

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.

Roland Wenzlofsky

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 20 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

You might also like

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

Never miss a good story!

 Subscribe to our newsletter to keep up with the latest trends!