A recent blog by 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 daily, let me tell you that precisely these functions 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 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.
Below the SQL that does the job:
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)
Alternative approaches 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.