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

Teradata Vantage™ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

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.

Support for hexadecimal name literals may be discontinued in a future database release. Use Unicode delimited identifiers when you need characters 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. Vantage converts a hexadecimal name literal to a UNICODE string to find the object name in the Data Dictionary.

With object naming, the following text files list the characters from the UNICODE server character set that are valid in object names and can appear in hexadecimal name literals. You can download the files here:
  1. Access Teradata Vantage™ - SQL Fundamentals, B035-1141 at https://docs.teradata.com/.
  2. In the left pane, select Attachments to download the Object_Name_Characters zip file.
File Name Title
UOBJNSTD.txt UNICODE in Object Names on Standard Language Support Systems
UOBJNJAP.txt UNICODE in Object Names on Japanese Language Support Systems

Restrictions

The minimal restrictions for object names are that the object name must not consist entirely of white space characters, and the following characters are not allowed in the name:
  • NULL (U+0000)
  • SUBSTITUTE character (U+001A)
  • REPLACEMENT CHARACTER (U+FFFD)
  • The compatibility ideographs U+FA6C, U+FACF, U+FAD0, U+FAD1, U+FAD5, U+FAD6, U+FAD7

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.

IF you want to find the internal representation of … THEN use the CHAR2HEXINT function on the …
a database name that you can use to form a hexadecimal name literal DatabaseName column of the DBC.Databases view.
a database name that you can use to form a Unicode delimited identifier DatabaseName column of the DBC.DatabasesV view.
a table, macro, or view name that you can use to form a hexadecimal name literal TableName column of the DBC.Tables view.
a table, macro, or view name that you can use to form a Unicode delimited identifier TableName column of the DBC.TablesV view.

Example: Finding Hexadecimal Name Literals in DBC.Tables [Deprecated]

This method is deprecated because it no longer works for all names. Instead, use the UESCAPE method shown in the next example.

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 Information

For more information about:
  • CHAR2HEXINT, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
  • Object names and name validation, see General Rules for Constructing Object Names.
  • Client character sets and object name restrictions, see Teradata Vantage™ - Advanced SQL Engine International Character Set Support, B035-1125.
  • DBC.TablesV view, see Teradata Vantage™ - Data Dictionary, B035-1092.
  • TableKind column, see Teradata Vantage™ - Data Dictionary, B035-1092.