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.