15.10 - Unicode Delimited Character Literals - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

Declares a Unicode delimited character literal value in an expression.

where:

 

Syntax element …

Specifies …

'Unicode_string_body'

a string of zero or more characters, enclosed in apostrophes, consisting of a combination of the following:

  • Any character other than an apostrophe ( ' ) or the Unicode escape character
  • Two consecutive apostrophes
  • Use two consecutive apostrophes to include an apostrophe character in the Unicode delimited character literal.

  • Two consecutive Unicode escape characters
  • Use two consecutive escape characters to include the escape character in the delimited character literal.

  • One Unicode escape character followed by four hexadecimal digits, where a hexadecimal digit is a character from 0 to 9, a to f, or A to F
  • The four hexadecimal digits represent a Unicode code point.

    The Unicode escape character is specified by the UESCAPE clause.

    If 'Unicode_string_body' immediately follows the U& key letters, no pad characters can appear before the first apostrophe.

    Use successive occurrences of 'Unicode_string_body' to concatenate multiple strings, separated by a SPACE (U+0020).

    UESCAPE 'Unicode_esc_char'

    a single character from the session character set to use as the Unicode escape character in the delimited character literal.

    The character must be within the printable ASCII range of Unicode characters (U+0021 through U+007E), with the following exceptions:

  • SPACE (U+0020)
  • QUOTATION MARK (U+0022)
  • APOSTROPHE (U+0027)
  • PLUS SIGN (U+002B)
  • hexadecimal digit (0-9, a-f, or A-F):
  • U+0030 to U+0039
  • U+0041 to U+0046
  • U+0061 to U+0066
  • You can also specify the YEN SIGN (U+00A5) and WON SIGN (U+20A9) as the Unicode escape character.

     

    Unicode delimited character literals are partially ANSI SQL:2011 compliant. The ANSI SQL:2011 standard does not require the UESCAPE clause and allows more possibilities for the Unicode_esc_char.

    A Unicode delimited character literal can consist of a maximum of 31000 Unicode_string_body characters.

    The data type of Unicode delimited character literals is VARCHAR(n) CHARACTER SET UNICODE, where n is the resolved length of the literal in Unicode characters.

    For details on the VARCHAR(n) type, see “VARCHAR Data Type” on page 208.

    A Unicode delimited character literal is useful for inserting a character string containing characters that cannot generally be entered directly on the terminal keyboard or is not available in the current session character set.

    If your application is intended to be ANSI-compliant and portable, you can replace existing hexadecimal character literals of the form 'hexadecimal digits'XC with Unicode character literals.

    Consider this table:

       CREATE TABLE TextTable
          (IDNum INTEGER
          ,Ustring VARCHAR(10) CHARACTER SET UNICODE);
     

    This statement uses a Unicode delimited character literal to insert the character string '' into the Ustring column, using the number sign (#) as the Unicode escape character:

       INSERT TextTable (10, _Unicode U&'#8CC7#6599#5009#5132' UESCAPE '#');

    This statement concatenates two strings to insert '855-34-9729' into the Ustring column:

       INSERT TextTable (11, U&'855-34-' '9729' UESCAPE '%');

    This statement selects all rows from the TextTable table where the string in the Ustring column is an empty string:

       SELECT * FROM TextTable WHERE Ustring = U&'' UESCAPE '&';