DWHPro Insights

secondary index

Teradata Table Design Checklist: Primary Index, Character Set, and Data Type Considerations Popular

Illustration of a stopwatch dial on an orange paint-splash background

Teradata Table Design Checklist As creating tables is a frequent task, I deemed it necessary to provide a checklist. 1. Primary Index Or NOPI Table? The Primary Index serves multiple purposes, primarily functioning as the primary access path to the data and being optimized for an even distribution of rows. However, designing a table without …

Read more

Teradata ASCII: Built-in Function and Legacy CHAR2HEXINT Example

Illustration of a stopwatch dial on an orange paint-splash background

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.

Protected by Security by CleanTalk and CleanTalk Anti-Spam