Extracting Numeric Tokens from Strings with CHAR2HEXINT Function in Teradata SQL
Learn how the CHAR2HEXINT function helped solve a real-world problem of extracting number parts from a string in Teradata SQL. Read on for the solution.
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.
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
We resolved the issue of the given solution's failure with special characters by extracting the desired substring only when every token is numeric.
Get the next issue by email.
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
ENDOther 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.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.