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.
Validate exactly the required three-character substring before converting it. Current Teradata regular-expression support provides a direct way to express this rule.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookThe example returns -9 for NULL, short or nonnumeric substrings. CHAR2HEXINT encoding is character-set dependent; the audited digit values were 0030 through 0039, not the former three-digit bounds.
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 REGEXP_SIMILAR(SUBSTR('1201',2,3), '^[0-9]{3}$', 'c') = 1
THEN CAST(SUBSTR('1201',2,3) AS INTEGER)
ELSE -9 END AS extracted_number;
REGEXP_SIMILAR is available on the audited release. The validation pattern was exercised with numeric, short, nonnumeric, trailing-space and NULL inputs before integer conversion.
Incorporate this feature into your repertoire of tools for handling tricky coding operations.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book