16.10 - Hexadecimal Name Literals - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Published
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)
Last Update
2018-04-25

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 Teradata 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. Teradata Database 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. These files are available on the Web at http://www.info.teradata.com/.

File Name Title (on the Web)
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

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.
This method, used in previous Teradata Database releases, no longer works for all names, and is deprecated future use. Instead, use the UESCAPE method shown in “Example: Finding Unicode Delimited Identifiers in DBC.TablesV.”

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

For more information about:

  • CHAR2HEXINT, see SQL Geospatial Types .
  • Object names and name validation, see “General Rules for Constructing Object Names” in “Object Names.”
  • Client character sets and object name restrictions, see International Character Set Support .
  • DBC.TablesV view, see Database Administration .
  • TableKind column, see Database Administration .