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.
- Access Teradata Vantage™ - SQL Fundamentals, B035-1141 at https://docs.teradata.com/.
- 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
- 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.
- Remove the excess space characters (202020...) from the result.
- Enclose the remaining numbers (those to the left of the space characters) in double quotation marks.
- 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:
- Copy the hexadecimal representation from the result of the SELECT CHAR2HEXINT request, for example:
00440062006100730065
- 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. - Convert the string to UNICODE delimiter format, as shown in Working with Unicode Delimited Identifiers, for example:
U&"x0044x0062x0061x0073x0065" UESCAPE 'x'
Related Topics
- 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.