Introduction
The tested Teradata release supports SELECT ASCII('B');, which returned 66. The CHAR2HEXINT expression below is a legacy encoding-dependent example, not a necessary replacement for the built-in function.
The solution is as follows:
We use the Teradata SQL function CHAR2HEXINT to obtain a hexadecimal value for a character. Our objective is to convert this hexadecimal value to its decimal counterpart, which signifies the character’s ASCII code.
The following legacy expression demonstrates the calculation for this specific character and encoding.
SELECT
CASE SUBSTRING(CHAR2HEXINT('B') FROM 3 FOR 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END * 16 +
CASE SUBSTRING(CHAR2HEXINT('B') FROM 4 FOR 1)
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END
Using a CASE statement, the query extracts two hexadecimal digits with the SUBSTRING function and converts them to decimal values. It then calculates the character’s ASCII code by multiplying the first digit’s decimal value by 16 and adding the second digit’s decimal value.
Conclusion
Prefer the documented ASCII function when its semantics match the task. The legacy workaround returned 66 for B with CHAR2HEXINT yielding 0042 in the tested context. Its fixed substring positions are not a general solution for arbitrary character sets or Unicode characters.
