Learn Teradata Indexing Techniques in this 2-Hour Video Course

Illustration of a glossy orange database cylinder with data fragments across its surface

This course focuses on Teradata indexing techniques. In this two-hour video, you will learn about Teradata’s architecture and Full Table Scans, different types of Primary Index Access such as UPI and NUPI, diverse Secondary Index Access including USI and NUSI, and Join Indexes. This historical video introduction covers indexing topics. The embedded video itself was …

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.

Why Teradata DBAs Should Prioritize Housekeeping the DBC AccessRights Table

Illustration of a person in a suit standing beside a database cylinder

As any experienced Teradata DBA will tell you, some tables in the Teradata dictionary (DBC) need housekeeping. But some customer sites that I have worked with ignore table DBC.AccessRights. If you have ever analyzed this table on a Teradata system, you will almost certainly spot two things quickly. Do not modify DBC.AccessRights directly. Fewer rows …

Read more

Is Teradata Compression Worth Your Time and Effort?

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

Teradata DBMS offers various compression options to save disk space and improve performance. But, the cost of implementing the compression takes man-time. This article discusses the cost vs. benefit of compression and suggests carefully considering the compression setting when designing new or modifying existing tables.

The Teradata RDBMS

Illustration of three stacked database units of differing heights inside a circle

Introduction Teradata is a name synonymous with data warehousing and large-scale data analytics. Teradata has provided organizations with innovative solutions and technologies for over four decades as a pioneer in the field. This article will delve into the fascinating world of Teradata, exploring its history, architecture, and diverse applications. We will also address some common …

Read more

Protected by Security by CleanTalk and CleanTalk Anti-Spam