15.00 - Working with Unicode Delimited Identifiers - Teradata Database

Working with Unicode Delimited Identifiers

The system allows the use of UNICODE delimited identifiers to enable specification of object names and literals having characters that are not compatible with the session character set.

System Use of Unicode Delimited Identifiers

Teradata Database uses Unicode delimited identifiers for:

Returning certain information as the result of running a console utility or executing a UDT, UDF, or stored procedure that contains an SQL TEXT element.

Returning results of an EXPLAIN, HELP or SHOW request for names that include characters not in the repertoire of the session character set, or that are otherwise unprintable.

For example, the table name:

In an ASCII session, where the table name does not translate into the session character set, the table portion of the HELP output appears similar to the following:

Table Name: ^Z^Z^Z^Z

Table Dictionary Name: ^Z^Z^Z^Z

Table SQL Name: U&"\FF83\FF70\FF8C\FF9E\FF99"

Table UEscape: \

where:

Output	Description
Table Dictionary Name: ^Z^Z ^Z^Z	Each ^Z represents an ASCII replacement character, 0x1A, used to replace the 4 untranslatable characters, . Note: The system uses ^ to represent unprintable control characters.
Table SQL Name: U&"\FF83\FF70\FF8C \FF9E\FF99"	The SQL Name begins with U& to indicate that it is a UNICODE delimited identifier, that is, it contains untranslatable characters. The sequence \FF83\FF70\FF8C\FF9E\FF99 is the set of UNICODE identifiers for the 4 untranslatable characters, preceded by the default delimiter character, in this case, \. The string is enclosed in quotation marks so that the delimiter characters it contains can be used in an SQL request. Note: The system does not return the UEscape clause, which normally closes a UNICODE delimited identifier. If you want to use a UNICODE delimited identifier in an SQL request, you need to add the UEscape clause. See Using an SQL Name or SQL Title Value in an SQL Request.
Table UEscape: \	Indicates the delimiter character used to separate the UNICODE identifiers in the SQL Name. For list of allowable delimiter characters, see “Determining the Delimiter Character” on page 86.

Using an SQL Name or SQL Title Value in an SQL Request

You can use the SQL Name or SQL Title of an object returned as the result of a HELP request in an SQL request.

For example, you can use HELP DATABASE to look in the mydb database for the name of a table you want to drop:

HELP DATABASE mydb;

… Table SQL Name U&"table_\4E00_name" 

   Table UEscape		 \ 

The SQL Name returned by the HELP DATABASE statement is a UNICODE delimited identifier, which includes an untranslatable character that the system expresses as \4E00.

If you want to use a UNICODE delimited identifier from the SQL Name as part of an SQL request, you must:

1 Add the closing UEscape clause to the table name taken from the SQL Name field.

2 Specify the delimiter character ( \ )

For example:

Drop Table U&"table_\4E00_name" UEscape '\';

If the SQL Name is not a UNICODE delimited identifier, you can use the name in an SQL request as it appears in the HELP output, without specifying the UEscape phrase and delimiter.

Determining the Delimiter Character

You can use one of the following delimiter characters in SQL Name and SQL title, depending on availability in the session character set:

BACKSLASH (U+005C)

TILDE (U+007E)

The YEN SIGN (U+00A5) or WON SIGN (U+20A9), depending on which is present in the session character set at 0x5C.

NUMBER SIGN (U+0023), which is present in all supported session character sets.

The UEscape field that follows each SQL Name field must be used to identify the delimiter used.