- \n
- UNISTR function for Unicode character representation<\/li>\n
- ANSI SQL string concatenation operator (
||<\/code>) for standard-compliant string operations<\/li>\n<\/ul>\nThese additions improve ANSI SQL compliance in Azure SQL, streamline migration from other platforms, and boost developer productivity.<\/div>\n<\/div>\n<\/div>\n![\"UNISTR](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2025\/07\/UNISTR_StrConct-1024x683.png\")
<\/a><\/div>\n\n<\/p>\n
UNISTR function: Unicode made easy<\/h2>\n
TheUNISTR<\/code> function provides support for Unicode string literals by letting you specify the Unicode encoding value of characters in the string, making it especially useful for working with international or special characters. If you want to include characters like emojis, accented letters, or symbols from non-Latin scripts,UNISTR<\/code> lets you encode them directly using their Unicode values.<\/div>\n\n- \n
- Returns Unicode characters<\/strong>\u00a0based on the input expression, following the\u00a0Unicode standard<\/strong>.<\/li>\n
- Supports\u00a0escape sequences<\/strong>:\n
- \n
\\xxxx<\/code>\u00a0for UTF-16 codepoints.<\/li>\n\\+xxxxxx<\/code>\u00a0for full Unicode codepoints.<\/li>\n<\/ul>\n<\/li>\n- Enables easy integration of\u00a0complex Unicode characters<\/strong> into strings. You can look up Unicode codepoint values in the\u00a0Unicode Code Charts<\/a>.<\/li>\n<\/ul>\n<\/div>\n
Comparison with NCHAR<\/strong><\/h4>\n
- \n
- NCHAR<\/strong>: Converts a\u00a0single Unicode value<\/strong>\u00a0to a character.<\/li>\n
- UNISTR<\/strong>: Handles\u00a0multiple Unicode values and escape sequences<\/strong>, offering\u00a0greater flexibility<\/strong>\u00a0for constructing strings with diverse characters.<\/li>\n<\/ul>\n
Syntax<\/h3>\n
<\/p>\n<\/div>\n
\nUNISTR ( 'character_expression' [ , 'unicode_escape_character' ] )<\/code><\/pre>\n- \n
- The data type of character_expression<\/em> could be\u00a0char<\/strong>,\u00a0nchar<\/strong>,\u00a0varchar<\/strong>, or\u00a0nvarchar<\/strong>. For\u00a0char\u00a0<\/strong>and\u00a0varchar<\/strong>\u00a0data types, the collation should be a valid\u00a0UTF-8 collation<\/a>\u00a0only.<\/li>\n
- A single character representing a user-defined Unicode escape sequence. If not supplied, the default value is
\\<\/code>.<\/li>\n<\/ul>\n<\/div>\n\n<\/p>\n<\/div>\n
Examples<\/h3>\n
Example #1:<\/h4>\n
In this example, Unicode value used with text literal:<\/p>\n
SELECT UNISTR (N'I \\2764 Azure SQL.') AS string_with_unicode <\/code><\/pre>\nResults:<\/div>\nI \u2764\ufe0f Azure SQL.<\/div>\n<\/h4>\n
<\/h4>\n
<\/p>\n
Example #2:<\/h4>\n
In this example, we construct a non-Latin script string using Unicode escape sequence.<\/p>\n
\nSELECT UNISTR(N'\\3053\\3093\\306B\\3061\\306F') AS Japanese_Greeting;<\/code><\/pre>\nResults:<\/div>\n\u3053\u3093\u306b\u3061\u306f<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n<\/i>Note<\/strong><\/p>The UNISTR function is not compatible with legacy code pages, meaning it does not support collations that use a code page.<\/p>\n
The legacy collations with code page can be <\/span>identified<\/span> using the query below:<\/span><\/span><\/p>\n
SELECT DISTINCT p.language, p.codepage \r\nFROM sys.fn_helpcollations() AS c CROSS APPLY (VALUES(LEFT(c.name, CHARINDEX('_', c.name)-1), COLLATIONPROPERTY(c.name, 'codepage'))) AS p(language, codepage)\r\nWHERE p.codepage NOT IN (0 \/* Unicode Only collation *\/, 65001 \/* UTF-8 code page *\/);<\/code><\/pre>\n<\/div><\/p>\n<\/div>\n
\n<\/p>\n<\/div>\n
<\/div>\n\n<\/h2>\n
ANSI SQL || Operator: A cleaner way to concatenate<\/h2>\n
The||<\/code> operator is now supported for string concatenation, offering a more readable and standard-compliant alternative to existing methods like+<\/code> andCONCAT()<\/code>. The\u00a0||<\/code> operator concatenates two or more characters or binary strings, columns, or a combination of strings and column names into one expression. The\u00a0<\/span>||<\/span><\/span><\/code>ope<\/span>ra<\/span>tor<\/span>\u00a0does not honor the\u00a0<\/span><\/span>SET CONCAT_NULL_YIELDS_NULL<\/a><\/span>\u00a0option<\/span>\u00a0and always\u00a0<\/span>behaves as if the ANSI SQL behavior is enabled, yielding\u00a0NULL<\/code>\u00a0if any of the inputs is\u00a0NULL<\/code>.<\/span>\u00a0<\/span>This\u00a0<\/span>ope<\/span>ra<\/span>tor<\/span>\u00a0will work with character strings<\/span>\u00a0or binary data<\/span>\u00a0of any supported SQL Server collation<\/span>.<\/span>\u00a0The\u00a0<\/span>||<\/span><\/span><\/code>ope<\/span>ra<\/span>tor<\/span>\u00a0supports\u00a0<\/span>compound assignment\u00a0<\/span>||<\/span><\/span>=<\/span><\/span><\/code>similar to<\/span><\/span>\u00a0+=<\/a><\/span>.<\/span>\u00a0If the\u00a0<\/span>ope<\/span>ra<\/span>nds are of incompatible collation, then an error will be thrown.<\/span>\u00a0The collation behavior\u00a0<\/span>is identical to the\u00a0CONCAT<\/a>\u00a0function<\/span>\u00a0of character string data.<\/span><\/span><\/div>\nSyntax<\/h3>\n
expression || expression<\/code><\/pre>\n- \n
- The\u00a0expression<\/a>\u00a0is a character or binary expression.\u00a0<\/span>Both expressions must be of the same data type, or one expression must be able to be implicitly converted to the data type of the other expression.<\/span>\u00a0If\u00a0<\/span>one<\/span>\u00a0ope<\/span>ra<\/span>nd is\u00a0<\/span>of<\/span>\u00a0binary type, then an unsupported\u00a0<\/span>ope<\/span>ra<\/span>nd type error will be thrown.<\/span><\/span><\/li>\n<\/ul>\n
Examples<\/h3>\n
Example #1:<\/h4>\n
For example, the following query concatenates two strings and returns the result:<\/p>\n
SELECT 'Hello ' || 'World!';<\/code><\/pre>\nResults:<\/p>\n
Hello World!<\/p>\n
Example #2:<\/h4>\n
In this example, multiple character strings are concatenated. If at least one input is a character string, non-character strings will be implicitly converted to character strings.<\/p>\n
SELECT \r\n -- Combine first and last name\r\n 'Jos\u00e8' || ' Doe' AS full_name,\r\n\r\n -- Construct a detailed order string with ID, timestamp, and unique identifier\r\n 'Order-' || CAST(1001 AS VARCHAR) || '~TS~' || current_timestamp || '~' || NEWID() AS order_details,\r\n \r\n -- Handle NULL safely in concatenation \r\n 'Item-' || NULL AS item_desc;<\/code><\/pre>\nResults:<\/p>\n
- A single character representing a user-defined Unicode escape sequence. If not supplied, the default value is
- UNISTR<\/strong>: Handles\u00a0multiple Unicode values and escape sequences<\/strong>, offering\u00a0greater flexibility<\/strong>\u00a0for constructing strings with diverse characters.<\/li>\n<\/ul>\n
- Supports\u00a0escape sequences<\/strong>:\n
- Returns Unicode characters<\/strong>\u00a0based on the input expression, following the\u00a0Unicode standard<\/strong>.<\/li>\n