Hexadecimal Name Literals | SQL Fundamentals | Teradata Vantage - Hexadecimal Name Literals - Advanced SQL Engine - Teradata Database

Hexadecimal name literals provide a means to create and reference object names by their internal UNICODE representation in the Data Dictionary, for example, because characters are not representable in the session character set.

ANSI Compliance

Hexadecimal name literals are Teradata extensions to the ANSI/ISO SQL:2008 standard.

Maximum Length

A hexadecimal name literal can consist of a maximum of 60 hexadecimal digits.

Usage

A hexadecimal name literal is useful for specifying an object name containing characters that cannot generally be entered directly from a keyboard.

Object names are stored and processed using the UNICODE server character set. Teradata Database converts a hexadecimal name literal to a UNICODE string to find the object name in the Data Dictionary.

Restrictions

Example: Querying the Session Character Set

Consider a table with the name TAB1 (in full-width Latin characters) on a system enabled with Japanese language support.

Use this query when the session character set is KANJIEBCDIC5035_0I to return all columns from the table:

SELECT * FROM '0E42E342C142C242F10F'XN;

Use this query when the session character set is KANJIEUC_0U to return all columns:

SELECT * FROM '8273826082618250'XN;

Finding the Internal Hexadecimal Representation for an Object Name

You can use the CHAR2HEXINT function to find the internal hexadecimal representation for any object name. For example, the table below shows how to find a database name.

Example: Finding Hexadecimal Name Literals in DBC.Tables

To find the internal hexadecimal representation for the table Dbase that in a form that converts to a hexadecimal name literal, select CHAR2HEXINT from DBC.Tables:

SELECT CHAR2HEXINT(T.TableName) (FORMAT 'X(60)',
   TITLE 'Internal Hex Representation'),T.TableName (TITLE 'Name')
FROM DBC.Tables T
WHERE T.TableKind = 'T' AND T.TableName = 'Dbase';

The system returns the result, for example:

Internal Hex Representation                                  Name
------------------------------------------------------------ --------------
446261736520202020202020202020202020202020202020202020202020 Dbase

You can use the result to reconstruct the object in the form of a hexadecimal name literal.

1. Remove the excess space characters (202020...) from the result.
2. Enclose the remaining numbers (those to the left of the space characters) in double quotation marks.
3. Add XN at the end.

The hexadecimal name literal for the preceding example is:

"4462617365"XN

To obtain the internal hexadecimal representation of the names of other objects types, modify the WHERE clause. For example, to obtain the internal hexadecimal representation of a view, modify the WHERE clause to TableKind = 'V'.

Similarly, to obtain the internal hexadecimal representation of a macro, modify the WHERE condition to TableKind = 'M'.

Example: Finding Unicode Delimited Identifiers in DBC.TablesV

You can also find the internal hexadecimal representation for any database object in DBC.TablesV view. These hexadecimal representations convert to Unicode delimited identifiers. For example, for the table Dbase:

SELECT CHAR2HEXINT(T.TableName) (FORMAT 'X(60)',
   TITLE 'Internal Hex Representation'),T.TableName (TITLE 'Name')
FROM DBC.TablesV T
WHERE T.TableKind = 'T' AND T.TableName = 'Dbase';

The system returns the result, for example:

Internal Hex Representation                                  Name
------------------------------------------------------------ --------------
00440062006100730065                                         Dbase

You can use the result to reconstruct the object name as a Unicode delimited identifier:

1. Copy the hexadecimal representation from the result of the SELECT CHAR2HEXINT request, for example:

   00440062006100730065

2. Add a delimiter character before each set of double zeros (00), for example:

   x0044x0062x0061x0073x0065

   You can use most printable (7-bit) ASCII characters as delimiters.
3. Convert the string to UNICODE delimiter format, as shown in Working with Unicode Delimited Identifiers, for example:

   U&"x0044x0062x0061x0073x0065" UESCAPE 'x'

Related Topics