Lenni's Technology Blog

This is an extremely simple post that covers an extremely simple new feature in T-SQL for string concatenation using the || operator.

Before SQL Server 2025, T-SQL offered two ways to concatenate strings:

• The + operator, which has been around forever.
• The CONCAT() function, introduced in SQL Server 2012.

In SQL Server 2025, Microsoft adds a third option: the ANSI-standard || operator. This operator is widely supported across other relational databases (PostgreSQL, Oracle, SQLite, etc.) and now works natively in T-SQL as well.

The || operator provides a more consistent and standards-compliant way to concatenate strings. Like the + operator (but unlike CONCAT), it propagates a single NULL value into a final NULL result. Conversely, like CONCAT (but unlike the + operator), it automatically converts non-string types to strings for concatentation.

Comparison of String Concatenation Methods

The following table summarizes the differences between the three methods of string concatenation in T-SQL:

String Concatenation with NULLs

Run the following code:

DECLARE @UserId int = NULL
SELECT PlusWithNulls    = 'User ID ' + CONVERT(varchar(max), @UserId)   -- Requires type conversion from int, a single NULL results in NULL
SELECT ConcatWithNulls  = CONCAT('User ID ', @UserId)                   -- Automatically converts int to string, any NULL values are converted to empty strings
SELECT AnsiWithNulls    = 'User ID ' || @UserId                         -- Automatically converts int to string (like CONCAT), a single NULL results in NULL (like +)
GO

Observe that for the first query (+), we must explicitly convert integer values to strings using CONVERT() or CAST(). Also, since @UserId is NULL, the whole result becomes NULL.

In the second query (CONCAT), integers (and other non-string types) are automatically converted to strings. And since @UserId is NULL, it is treated as an empty string (''), resulting in User ID .

The third query (||) uses the ANSI standard operator for string concatentation. It behaves like CONCAT in that it handles implicit type conversion (that is, no CONVERT() or CAST() is needed). However, it behaves like + in terms of NULL handling. And so since @UserId is NULL, the whole result is NULL.

String Concatenation without NULLs

Now run the same code again, only this time set @UserId to a non-NULL value:

DECLARE @UserId int = 16
SELECT PlusWithNonNulls     = 'User ID ' + CONVERT(varchar(max), @UserId)    -- Requires type conversion from int, a single NULL results in NULL
SELECT ConcatWithNonNulls   = CONCAT('User ID ', @UserId)                    -- Automatically converts int to string, any NULL values are converted to empty strings
SELECT AnsiWithNonNulls     = 'User ID ' || @UserId                          -- Automatically converts int to string (like CONCAT), a single NULL results in NULL (like +)
GO

Observe that all three return the same result: User ID 16, since there are no NULL values in the concatenation. Yet still, the + operator requires explicit type conversion, while both CONCAT and || handle it automatically.

Summary

• Use + when working with legacy SQL Server versions, but handle NULLs and casts manually.
• Use CONCAT() when you want NULL safety and implicit conversion, especially for multiple values.
• Use || when you want standards-compliant syntax with the benefit of implicit casting (but still handling NULLs manually) and are using SQL Server 2025 or later.