SQL: ANSI string concatenation with the || and ||= operators

One change that was applied to Azure SQL Database a while back, and is coming in SQL Server 2025, is the use of ANSI string concatenation operators.

SQL Server has been using the + sign as a string concatenation operator since the early days, but that’s the same operator that’s used for numbers, and it’s not the ANSI standard. You’ll find that other database engines like PostgreSQL do not use + to join strings together; they use the || operator. I’ve been writing quite a lot of PostgreSQL lately, and avoiding using + to concatenate strings is always a challenge for my coding muscle memory.

SQL Server is now offering this operator, but it has a few qwirks that I think it’s important to understand.

First, with data types, it works with character expressions, binary strings, and columns, but not with XML, JSON, image, ntext, or text. For the first two, you can cast them to a string first and then use it, and for the other three, you shouldn’t be using them now anyway.

The value is truncated to 8000 bytes unless you’re using a LOB type.

Using the Operators

If I execute the following code:

SELECT 'Hello' + ' There';

SELECT 'Hello' || ' There';

Then I see the following output:

Hello There
Hello There

So no surprises there.

NULL handling

But what if NULL is involved ?

SELECT 'Hello' + NULL + ' There';

SELECT 'Hello' || NULL || ' There';

That returns:

NULL
NULL

Again, that’s as we’d expect. It returns NULL on NULL input.

CONCAT_NULL_YIELDS_NULL

But notice what happens if I use the CONCAT_NULL_YIELDS_NULL session option:

SET CONCAT_NULL_YIELDS_NULL OFF;
GO

SELECT 'Hello' + NULL + ' There';

SELECT 'Hello' || NULL || ' There';
GO

SET CONCAT_NULL_YIELDS_NULL ON;
GO

That returns the following:

Hello There
NULL

Importantly, the new operator ignores the CONCAT_NULL_YIELDS_NULL option.

Compound operator

As well as the standard concatenation operator, we did get the compound version as well:

DECLARE @Value varchar(100) = 'Hello';

SET @Value ||= ' There';
SELECT @Value;

This also returns:

Hello There

Binary strings

As well as standard text strings, the new operators also work with binary strings:

DECLARE @Value varbinary(100) = 0x1a1a;

SET @Value ||= 0x2b2b;
SELECT @Value;

That returns:

0x1A1A2B2B

Implicit casting

Note that because this operator knows it’s working with strings, that implicit casting also works with this operator:

SELECT 'Hello' || ' There ' || 5;

SELECT 5 || 'Hello' || ' There ';

This returns:

Hello There 5
5Hello There

It’s great to see this new addition to T-SQL.

2025-11-26