15.00 - Hexadecimal Representation of Object Names - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Hexadecimal Representation of Object Names

All object names are stored in the Data Dictionary in UNICODE. However, in an international environment, some client users may find that their client (session) character set cannot express an object name created on another client. In these cases, clients may find it useful to use a hexadecimal representation of the unavailable UNICODE characters in the object name to more easily access the object.

Using Delimiters with Hexadecimal Identifiers

When you reference a database object using a hexadecimal representation of the object name, you must express it as a name literal or enclose the name in UNICODE delimiters.

The best practice is to use UNICODE delimited identifiers because the hexadecimal value remains the same across all supported character sets, whereas using hexadecimal name literals allows the name representation to vary depending on the character set.

Example  

Consider a table name of where the translation to the UNICODE server character set is equivalent to the following Unicode delimited identifier:

   U&"#FF83#FF70#FF8C#FF9E#FF99" UESCAPE '#'

From a client where the client character set is KANJIEUC_0U, you can access the table using the following hexadecimal name literal:

   '80C380B080CC80DE80D9'XN

From a client where the client character set is KANJISJIS_0S, you can access the table using the following hexadecimal name literal:

   'C3B0CCDED9'XN

Example  

Assume that a hexadecimal name literal that represents the name TAB1 using full width Latin characters from the KANJIEBCDIC5035_0I character set on a system enabled for Japanese language support:

   SELECT EmployeeID FROM "0E42E342C142C242F10F"XN;

Teradata Database converts the hexadecimal name literal from a KANJI1 string to a UNICODE string to find the object name in the Data Dictionary.

Here is an example of a Unicode delimited identifier that represents the name TAB1 on a system enabled for Japanese language support:

   SELECT EmployeeID FROM U&"#FF34#FF21#FF22#FF11" UESCAPE '#';

Hexadecimal Name Literals

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.

Note: 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.

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 Teradata User Documentation CD and on the Web at http://www.info.teradata.com.

 

File Name (on CD)

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:

  • The object name must not consist entirely of white space characters.
  • 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  

    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;

    Related Topics

     

    FOR details on …

    SEE …

    object names and name validation

    “General Rules for Constructing Object Names” on page 78.

    client character sets and object name restrictions

    International Character Set Support.

    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.

    For details on CHAR2HEXINT, see SQL Functions, Operators, Expressions, and Predicates.

    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 quotes.

    3 Add XN at the end.

    Note: 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 2: Finding Unicode Delimited Identifiers in DBC.TablesV” on page 90.

    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'.

    For more information, see the TableKind column in Data Dictionary.

    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

    Note: 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” on page 85, for example:

       U&"x0044x0062x0061x0073x0065" UESCAPE 'x'

    For more information on the DBC.TablesV view, see Data Dictionary.