• UNISTR function for Unicode character representation
• ANSI SQL string concatenation operator (||) for standard-compliant string operations

UNISTR function: Unicode made easy

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 \.

SELECT UNISTR(N'\3053\3093\306B\3061\306F') AS Japanese_Greeting;

ANSI SQL || Operator: A cleaner way to concatenate

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()

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.

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