Extracting Numeric Tokens from Strings with CHAR2HEXINT Function in Teradata SQL

Roland Wenzlofsky’s recent blog introduced the CHAR2HEXINT function, which extracts coding table numbers for tokens.

These functions proved invaluable in solving a practical issue we encountered. If this seems irrelevant to your daily concerns, consider their practical application.

Given a string with a combination of characters and numbers, including special characters, extract the numerical portion and return it as a number if it exists. Otherwise, return a default value.

We resolved the issue of the given solution’s failure with special characters by extracting the desired substring only when every token is numeric.

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

Beware that using shortcuts like converting the substring “12 ” (one-two-blank) to an integer can create a deceptive sense of security.

Here is the SQL code that performs the task:

SELECT
CASE WHEN CHAR2HEXINT( SUBSTR(‚'1201'', 2, 1) ) BETWEEN ‚'030''AND ‚'039''AND
CHAR2HEXINT( SUBSTR(‚'1201'' 3, 1) ) BETWEEN ‚'030''AND ‚'039''AND
CHAR2HEXINT( SUBSTR(‚'1201'', 4, 1) ) BETWEEN ‚'030''AND ‚'039''
THEN SUBSTR(‚'1201'', 2, 3)
ELSE -9
END

Other approaches I used in previous data warehousing settings, such as implementing regular expressions through Oracle SQL, are not as applicable in Teradata SQL.

Incorporate this feature into your repertoire of tools for handling tricky coding operations.