UNISTR (Transact-SQL)

  • Article

Applies to: Azure SQL Database

UNISTR provides support for Unicode string literals by letting you specify the Unicode encoding value of characters in the string. UNISTR returns the Unicode characters, as defined by the Unicode standard, for the input expression.

The escape sequence for a Unicode character can be specified in the form of \xxxx or \+xxxxxx, where xxxx is a valid UTF-16 codepoint value, and xxxxxx is a valid Unicode codepoint value. You can look up Unicode codepoint values in the Unicode Code Charts.

Transact-SQL syntax conventions

Syntax

UNISTR ( 'character_expression' [ , 'unicode_escape_character' ] )

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Arguments

'character_expression'

An expression of any character type, such as char, nchar, varchar, or nvarchar. For char and varchar data types, the collation should be a valid UTF-8 collation.

N'unicode_escape_character'

A single character representing a user-defined Unicode escape sequence. If not supplied, the default value is \.

Return types

A string value whose length and type depend on the input types.

Examples

A. Use UNISTR vs the NCHAR function

The following examples all use the UNISTR functions to perform the necessary conversion of the Unicode values into string character set, to display the unicode character Smiling Face With Open Mouth. The database collation must be a UTF-8 collation if the input is of char or varchar data types.

Using UNISTR and NCHAR:

SELECT N'Hello! ' + NCHAR(0xd83d) + NCHAR(0xde00);

This sample example can also be written:

SELECT UNISTR(N'Hello! \D83D\DE00');

SELECT UNISTR(N'Hello! \+01F603');

Here is the result set.

-----------
Hello! 😃

B. Use UNISTR function with user defined escape character

The following example uses the UNISTR function with a custom escape character to perform the necessary conversion of the Unicode into a string character set.

SELECT UNISTR(N'ABC#00C0#0181#0187', '#');

Here is the result set.

-----------
ABCÀƁƇ

Related content