AzureFeeds

We’re excited to announce the General Availability (GA) of two long-standing capabilities that address critical needs for SQL developers and enterprise customers in Azure SQL Database and Azure SQL Managed Instance, configured with the Always-up-to-date update policy:

• UNISTR function for Unicode character representation


• ANSI SQL string concatenation operator (`||`) for standard-compliant string operations

These additions improve ANSI SQL compliance in Azure SQL, streamline migration from other platforms, and boost developer productivity.

UNISTR function: Unicode made easy

The UNISTR 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 lets you encode them directly using their Unicode values.

• Returns Unicode characters based on the input expression, following the Unicode standard.


• Supports escape sequences:
  
  
  
  • xxxx for UTF-16 codepoints.
  
  
  • +xxxxxx for full Unicode codepoints.
  
  
  
  


• Enables easy integration of complex Unicode characters into strings. You can look up Unicode codepoint values in the Unicode Code Charts.

Comparison with NCHAR

• NCHAR: Converts a single Unicode value to a character.


• UNISTR: Handles multiple Unicode values and escape sequences, offering greater flexibility for constructing strings with diverse characters.

Syntax

UNISTR ( ‘character_expression’ [ , ‘unicode_escape_character’ ] )

• The data type of character_expression could be char, nchar, varchar, or nvarchar. For char and varchar data types, the collation should be a valid UTF-8 collation only.


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

Examples

Example #1:

In this example, Unicode value used with text literal:

SELECT UNISTR (N’I 2764 Azure SQL.’) AS string_with_unicode

Results:

I ❤️ Azure SQL.

 

Example #2:

In this example, we construct a non-Latin script string using Unicode escape sequence.

SELECT UNISTR(N’30533093306B3061306F’) AS Japanese_Greeting;

Results:

こんにちは

 

The UNISTR function is not compatible with legacy code pages, meaning it does not support collations that use a code page.

The legacy collations with code page can be identified using the query below:

SELECT DISTINCT p.language, p.codepage FROM sys.fn_helpcollations() AS c CROSS APPLY (VALUES(LEFT(c.name, CHARINDEX(‘_’, c.name)-1), COLLATIONPROPERTY(c.name, ‘codepage’))) AS p(language, codepage) WHERE p.codepage NOT IN (0 /* Unicode Only collation */, 65001 /* UTF-8 code page */);

 

ANSI SQL || Operator: A cleaner way to concatenate

The || operator is now supported for string concatenation, offering a more readable and standard-compliant alternative to existing methods like + and CONCAT(). The || operator concatenates two or more characters or binary strings, columns, or a combination of strings and column names into one expression. The || operator does not honor the SET CONCAT_NULL_YIELDS_NULL option and always behaves as if the ANSI SQL behavior is enabled, yielding NULL if any of the inputs is NULL. This operator will work with character strings or binary data of any supported SQL Server collation. The ||operator supports compound assignment ||= similar to +=. If the operands are of incompatible collation, then an error will be thrown. The collation behavior is identical to the CONCAT function of character string data.

Syntax

expression || expression

• The expression is a character or binary expression. 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. If one operand is of binary type, then an unsupported operand type error will be thrown.

Examples

Example #1:

For example, the following query concatenates two strings and returns the result:

SELECT ‘Hello ‘ || ‘ World!’;

Results:

Hello World!

 

Example #2:

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.

SELECT
— Combine first and last name
‘Josè’ || ‘ Doe’ AS full_name,

— Construct a detailed order string with ID, timestamp, and unique identifier
‘Order-‘ || CAST(1001 AS VARCHAR) || ‘~TS~’ || current_timestamp || ‘~’ || NEWID() AS order_details,

— Handle NULL safely in concatenation
‘Item-‘ || NULL AS item_desc;

Results:

Example #3:

In this example, the use of || operator is unsupported when used with only non-character types or when combining binary data with other types.

— Attempting to concatenate two numeric literals: 1 and 2
SELECT 1 || 2;

— Attempting to concatenate integer, string, and binary literals
SELECT 1 || ‘a’ || 0x4e;

Above queries will fail with error messages as below –

The data types int and int are incompatible in the concat operator.
The data types varchar and varbinary are incompatible in the concat operator.

Comparison with + and CONCAT()

Let’s explore how || differs from + and CONCAT() in Azure SQL using real examples.

Sample queries:

— Binary + String
SELECT CONCAT(0x01, ‘a’); — Returns: a
SELECT 0x01 || ‘a’; — Returns: Error
SELECT 0x01 + ‘a’; — Returns: Error

— Binary + Binary
SELECT CONCAT(0x01, 0xff); — Returns: ÿ
SELECT 0x01 || 0xff; — Returns: 0x01FF
SELECT 0x01 + 0xff; — Returns: 0x01FF

— NULL Handling
SELECT CONCAT(NULL, ‘a’); — Returns: ‘a’
SELECT NULL || ‘a’; — Returns: NULL
SELECT NULL + ‘a’; — Returns: NULL

— Numeric + String
SELECT CONCAT(1, ‘a’); — Returns: ‘1a’
SELECT 1 || ‘a’; — Returns: ‘1a’
SELECT 1 + ‘a’; — Returns: Error

— Date + String
SELECT CONCAT(CURRENT_TIMESTAMP, ‘a’); — Returns: Jul 29 2025 9:29PMa
SELECT CURRENT_TIMESTAMP || ‘a’; — Returns: Jul 29 2025 9:29PMa
SELECT CURRENT_TIMESTAMP + ‘a’; — Returns: Error

Key Differences

Why these matters

• Easier Migration – Migrating from other databases becomes smoother with support for UNISTR and ||, reducing the need for syntax rewrites.


• Global Reach – UNISTR simplifies Unicode handling, making it easier to build apps for international markets.


• Cleaner Code – The || operator improves readability and aligns with modern SQL practices.

 Learn more

• UNISTR (Transact-SQL) – SQL Server | Microsoft Learn


• || (String Concatenation) (Transact-SQL) – SQL Server | Microsoft Learn


• ||= (Compound assignment) (Transact-SQL) – SQL Server | Microsoft Learn

Conclusion

In this blog post, we announced the General Availability (GA) of UNISTR function and ANSI SQL string concatenation operator (||) in Azure SQL.  The UNISTR function allows you to escape Unicode characters, making it easier to work with international text. ANSI SQL || operator provides a simple and intuitive way to combine characters or binary data.

These enhancements reflect our commitment to making Azure SQL the most developer-friendly, standards-compliant cloud-first database platform. Whether you’re modernizing legacy systems, building multilingual apps, or writing cleaner SQL, these features are designed to make your journey smoother.

We hope you will explore these enhancements, apply them in your projects, and share your feedback with us to help us continue improving. Thank you!