I was looking around for stuff to post about the other day, so I read through the latest What’s new in SQL Server 2025 and I found this:

|| (String concatenation)
Concatenate expressions with expression || expression.

My interest was piqued to say the least. Using + to concatenate string data has always had its issues, and as much as I love the CONCAT function, it is kind of clunky to use versus a proper operator.

There is also a CONCAT_WS function that will add a seperator in your output, which I won’t talk about more in this blog.

I will take the scenic route to get to the new operator, but if you want to skip that, just click here to go to the section that covers it.

Background

Pprobably every programmer’s second task, right after writing out 'Hello World' is to change the output to include some other value, in T-SQL, you might do:

DECLARE @variable varchar;
SET @variable = 'Bob';
SELECT 'Hello' + @variable;

Of course the next thing is to remedy the fact that we just output HelloBob with no space between the values, but if you can think back to your early days as a programmer, the first time you created a variable was exciting stuff. In T-SQL, we have all done something like the following:

SELECT 'Start-' + 'Middle' + '-End';

For simplicity, I will use a literal for the middle value in my examples, but usually we are formatting some data from a column in a database. This outputs:

----------------
Start-Middle-End

Which as always, works well enough when testing with simple, clean data.

NULL values

But then, as we work with data, NULL values come in:

SELECT 'Start-' + NULL + '-End';

And lo and behold, we get a NULL output.

----------------
NULL

There is good reason for this, which works a lot better in math, because NULL means unknown, and 1 + unknown is clearly not calculatable, but 'Start' + any value, known or unknown should still in all typical cases start with 'Start'. (There probably are characters where this is not true, and I am not smart enough to be on the committee that decides these things!)

Numbers

So NULL values aside, there are other issues, like in the following statement we hope to get a return value of Start-10-End.

SELECT 'Start-' + 10 + '-End';

Yet what we get is an error:

-----------
Msg 245, Level 16, State 1, Line 17
Conversion failed when converting the varchar value 'Start-' to data type int.

Date values

Date values seem like they should work. When you create a date value, we use a string literal, right?

SELECT 'Start-' + SYSDATETIME() + '-End';

No, date values fail as well:

Msg 402, Level 16, State 1, Line 23
The data types varchar and datetime2 are incompatible in the add operator.

Note the slight difference here, where there is no column header output, so this error was earlier than the integer one.

Uniqueidentifiers (Guids)

At this point you may be sensing a pattern, but the following statement:

SELECT 'Start-' + NEWID() + '-End';

This also is not going to give us what we were hoping, even though just like dates, there is an obvious string representation of a guid:

Msg 402, Level 16, State 1, Line 30
The data types varchar and uniqueidentifier are incompatible in the add operator.

The existing tricks to handle this

There are lots of different tricks to deal with this in SQL Server code, depending on the situation. For example, you can employ functions like ISNULL, COALESCE, CAST OR CONVERT:

SELECT 'Start-' + ISNULL(NULL,'') + '-End', 
       'Start-' + CAST(10 as varchar(10)) + '-End';

This gives us the output, handling the NULL value in the first column of the output, and the second the value of 10.

---------------- -------------------------
Start--End       Start-10-End

No matter what else you learn here in this article, These are not functions you need to forget. There are clear reasons to use these when you need more control over your handling of string concatenation, no matter what methods you are using.

Of course, in SQL Server 2012 (a very long time ago at this point), Microsoft added the CONCAT function. This handled the “simple” cases like this::

SELECT CONCAT('Start-', NULL,  '-End'), 
       CONCAT('Start-', 10,  '-End');

Which returns:

--------------- ---------------------------
Start--End      Start-10-End

CONCAT handles NULL values by treating them as an empty string. This is great when dealing with data that it is okay that you don’t know, like a middle name. But it can be really confusing if you are dealing with data where you didn’t expect there to be NULL values because it does not throw even a warning.

To repeat all of the examples from earlier:

SELECT CONCAT('Start-', NEWID(), '-End'), 
       CONCAT('Start-', SYSDATETIME(),'-End');

This returns a row with an embedded guid in the first value, and a really detailed time value in the second.

--Note: one row of output, broken into two lines for formatting:
-------------------------------------------------- 
Start-19788F6E-D2D4-4BE2-8710-2A7A03C5C5BC-End     
--------------------------------------------------
Start-2025-10-13 23:07:24.9661700-End

The || operator

But can we do better? Of course! In SQL Server 2025, the new concatenation operator is being added. Much like the CONCAT function, this method is a SQL Standard function. The concatenation operator is ||, and is awesome because it is a simple function to use, much like the + operator, but slightly different in that it will handle different datatypes, but NULL is a different story.

This operator works on string or binary data, and just like in most operations in SQL Server string manipulation, unless you use a (max) datatype (varchar(max), nvarchar(max), etc), the output is limited to 8000 bytes. It works very much like the CONCAT function, with one large difference. NULL handling. Fortunately, it will integrate datatypes where there is a simple textual representation.

Examples:

SELECT 'Start-' || 10 || '-End';

This returns:

----------------------
Start-10-End

Note that one of the entries must be a string value. For example: SELECT 10 || '10'; will return a varchar value of 1010. But SELECT 10 || 10; will return an error: Msg 402, Level 16, State 1, Line 129 / The data types int and int are incompatible in the concat operator.

And then with a guid:

SELECT 'Start-' || newid() || '-End';

This returns:

--------------------------------------------------
Start-EC6ACA8C-B311-47C2-8CD4-B0C079545AEC-End

For a date:

SELECT 'Start-' || sysdatetime() || '-End';

This returns:

--------------------------------------------------
Start-2025-10-12 22:59:03.6890156-End

Of course, a lot of times you won’t want to leave a data value like this, but it certainly could be useful in some cases.

But when a null is introduced into the equation:

SELECT 'Start-' || NULL || '-End';

You get a NULL:

-----------
NULL

This is because, as a standard function, it adheres to the base tenet that NULL +,/,=,* etc is always NULL. Which is awesome or not awesome, depending on your goals. Most of the time I am using CONCAT instead of +, it is specifically so I can hide/ignore nulls.

Being a SQL standard function, it will not honor the SET CONCAT_NULL_YIELDS_NULL setting, so any NULL in the expression will result in a NULL output.

A favorite trick of mine is combining + and CONCAT when formatting a name, and this particular use will work with || or +

DECLARE @FirstName nvarchar(50) = 'First',
        @MiddleName nvarchar(50),
        @LastName nvarchar(50) = 'Last' --Typically NOT NULL as a column
SELECT CONCAT(@FirstName + ' ', @MiddleName + ' ', @LastName);
SELECT CONCAT(@FirstName || ' ', @MiddleName || ' ', @LastName);

The output is two outputs with the same value, because the NULL @MiddleName concatenated with ' ' in the CONCAT function will return '', not NULL:

---------------
First Last

Only one space, because @MiddleName (or NULL) + ' ' returns NULL, so it is ignored.

Compound Assignment

There is also a shortcut to adding a value to the end of a value. It works just like += does for numeric (and string) data, but is written ||=. (I will fully admit to the fact that I never really learned the += method and preferred the terse way of doing what it does, so if you are like me, let me give a quick example).

To add 1 to a variable:

DECLARE @Variable int = 0;
SET @Variable += 1;
SELECT @Variable;

This returns

-----------
1

Basically this is shortcut for “Take the right input value, and add it to the value of @Variable, then store it in the variable.”

It works with strings too:

DECLARE @Variable varchar(20) = 'a';
SET @Variable += 'b';
SELECT @Variable;

This outputs:

--------------------
ab

This new version just does this the same, but it also includes other datatypes just like ||

DECLARE @Variable varchar(20) = 'a';
SET @Variable ||= 'b';
SELECT @Variable
SET @Variable ||= sysdatetime() --it will be truncated by the variable length
SELECT @Variable

This returns the following two output values:

--------------------
ab
--------------------
ab2025-10-12 23:12:1

One quick binary example

For the most part, the main use of these functions is for string data. But it is not the only use. You can concatenate binary values as well. For example:

DECLARE @Variable varbinary(100)
-- cast a value to a binary value
SET @Variable = cast('Hello' as varbinary(100))
--next concatenate another binary value that equates to ' Hello'
SET @Variable = @Variable || cast(' Hello' as varbinary(100))
--case the output so we can see it:
SELECT @variable; --see the hex version
SELECT cast(@Variable as varchar(30)); --the text version

Two rows of output:

---------------------------
0x48656C6C6F2048656C6C6F
------------------------------
Hello Hello

A caveat is that it will not let you concatenate non-binary data. You will need to cast it first. If you try:

[code lang=”SQL”]
DECLARE @Variable varbinary(100);

SET @Variable = cast(‘Hello’ as varbinary(100));

SET @Variable = @Variable || ‘Test’; –concatenate the string literal ‘Test’
[/code]

Unlike strings, this will return:

Msg 402, Level 16, State 1, Line 156
The data types varbinary and varchar are incompatible in the concat operator.

Summary

There are so many ways to work with string data in SQL Server, and in SQL Server 2025, there is one more way via the new (to SQL Server) concatenation operator ||. It is an interesting operator that is more or less in between + and CONCAT when trying to concatenate string values, based on its handling of NULL values. So when you need NULL values to cause NULL output, but you want the extra power of being able to concatenate other data into your queries, this is a great use.

For example, something where you want to format a number with a prefix. You can easily write:

[code light=”true”]
‘Prefix-‘ || NumericValue
[/code]

And it will be NULL when NumericValue is NULL and Prefix-Number when it isn’t. Very cool.