Like many of you, I have often put strings together (concatenation) with a simple arithmetical operator: +.  We have a few other ways to put strings together, but in SQL Server 2025, we have a new operator that allows us to put strings together. This is the double pipe (||) operator. This article looks at how this works and compares it with the other options.

With a new version of SQL Server coming, I wanted to cover some of the T-SQL code changes. This is part of a series on how the T-SQL language is evolving in this version.

Note: some of these changes are already available in the various Azure SQL products.

The New Operator

This is actually documented as the string concatenation operator, though after the || in MS Learn. The idea is this will concatenate the following:

• two character strings
• two binary strings
• two columns
• a combination of the above

Why do we have this when we already have + and CONCAT()? This brings SQL Server in line with the ANSI Standard. Oracle and other products support this (as do Azure data products). With SQL Server 2025, we have this on-premises.

The syntax for this is like the + operator:

expression || expression

Let's look at this in practice.

Working with Literal Strings

Let's start by looking at string expressions. It's American Football season as I write this, so let's look at relevant code. If I run this code:

SELECT 'Denver' || 'Broncos'

As you might expect, I get this:

DenverBroncos

No spaces, just the two strings smashed together. I can add spaces in the strings, or I can add a third literal. Here are all three in code:

SELECT 
  'Denver ' || 'Broncos' AS Spacein1stString,
  'Denver ' || ' Broncos' AS Spacein2ndString,
  'Denver' || ' ' || 'Broncos' AS ThreeStrings

Now I get something better for all three: Denver Broncos.

What about variables? Let's try a few different items with some player names and numbers.

DECLARE @player varchar(20) = 'Bo Nix ',
@num VARCHAR(2) = '10'

SELECT @player || @num

This returns "Bo Nix 10', as you can see here. I also have included literal expressions as a few cases, so you can see that it doesn't matter if I mix variables and literals.

This is handy and works well. What about NULL values? Let's declare a position and forget to define it. I'm sure you haven't done this, but I have (or find the db query doesn't return a value).

DECLARE @player VARCHAR(20) = 'Bo Nix',
        @num VARCHAR(2) = '10',
@position VARCHAR(2);

SELECT @player || ' - ' || @num || '-' || @position

You might expect this to go one of two ways: the @position is treated an empty string and nothing returned, or it is NULL and that is returned. The latter is true.

What about conversions? Can I mix data types? I'll try a few things. Since I have a number, let me try a real number, a number in a variable, and a date. Here is the code to conduct a few experiments. I've changed to a series of SELECTs to get the results on one screen.

DECLARE @player VARCHAR(20) = 'Bo Nix',
        @num VARCHAR(2) = '10',
@position VARCHAR(2) = 'QB';

SELECT @player || ' - ' || @num || '-' || @position
UNION SELECT
@player || ' - ' || 10 || ' - ' || @position
UNION select
@player || ' - ' || @num || '-' || @position || ' Last Start:' || GETDATE()
UNION select
@player || ' - ' || @num || '-' || @position || ' Next Start:' || CAST('2025-10-24' AS DATE)

This returns a nice set of strings. The conversions between types are handled as we would expect.

I can mix in some data types, like numerics:

DECLARE @player VARCHAR(20) = 'Bo Nix',
        @num VARCHAR(2) = '10',
@position VARCHAR(2) = 'QB'

SELECT 
 @player || ' - ' || @num || ' - ' || @position || ' CMP%:' || 64.6 || ' DoB:' || CAST('2000/2/25' AS DATE)

This gives me what I expect. However, JSON doesn't work. Interesting the error specifics the concat operator.

I can also work with binary strings. Let's convert a string to binary and see what happens. I will cast two strings to binary and then get the result and cast that back.

DECLARE @b VARBINARY(100) = CAST('Next Opponent:' AS VARBINARY(100))
      , @o VARBINARY(100) = CAST('Giants' AS VARBINARY(100)) ;
SELECT @b || @o

SELECT CAST(@b || @o AS varchar(100))

The results? This works just like with strings.

However, I can't mix things. If I add a string in the middle, this fails.

I have to make everything the same.

There are other combinations of data types, but I didn't test everything. As long as things can be implicitly converted, this should work, but for some types, like JSON to JSON, this doesn't make sense. It's string concatenation, not variable concatenation.

Working with Columns

For the most part, working with columns is the same as working with literals or variables. Let's work with Northwind a bit. I'll put the ID with the company name with this code:

SELECT CustomerID || ':' || CompanyName AS Customer
 FROM dbo.Customers

Note that I've added a literal in the middle, and this works fine.

Any of the other caveats or things to know would align with the tests I ran above with the expressions.

Differences from + and CONCAT

The plus (+) operator is the way many people have concatenated strings for years. In many ways, this is the same with the ||, but there are some advantages. For simple things, this works well, such as this code, with both + and ||.

DECLARE @player varchar(20) = 'Bo Nix ',
        @num VARCHAR(2) = '10'

SELECT @player + @num,
       @player || @num

This works and I get results for both columns in the SELECT.

However, this code doesn't work.

DECLARE @player VARCHAR(20) = 'Bo Nix',
        @num VARCHAR(2) = '10',
@position VARCHAR(2) = 'QB'

SELECT 
 @player + ' - ' + @num + ' - ' + @position + ' CMP%:' + 64.6 + ' DoB:' + CAST('2000/2/25' AS DATE)

The datatype conversions don't work. The error below comes from the + operator and the number variable, @num. I would get a similar error from the date as well, but this code is the same as I used above in an example with ||.

While + is a habit for many people, the simplicity of avoiding extra conversion code would make me push to switch to ||.

CONCAT() is a better function to use. I can do the same examples with CONCAT and they work well.

DECLARE @player VARCHAR(20) = 'Bo Nix',
        @num VARCHAR(2) = '10',
@position VARCHAR(2) = 'QB'

SELECT 
   @player || ' - ' || @num || ' - ' || @position || ' CMP%:' || 64.6 || ' DoB:' || CAST('2000/2/25' AS DATE)
   UNION ALL SELECT
   CONCAT(@player, ' - ', @num, ' - ', @position, ' CMP%:', 64.6, ' DoB:', CAST('2000/2/25' AS DATE))
go

This works fine and I get see both results are the same. I think the function call vs for operator is definitely a big code change, but it works.

The one interesting place where CONCAT has a good advantage is with NULL. Let's look at this example. Here we'll go back to the time when I have a variable that I haven't assigned.

DECLARE @player VARCHAR(20) = 'Bo Nix',
        @num VARCHAR(2) = '10',
    @position varchar(2)

SELECT 
   @player || ' - ' || @num || ' -' || @position || '-2025 Season'
   UNION ALL SELECT
   CONCAT(@player, ' - ', @num, ' -', @position, '-2025 Season')
go

This means a NULL for the @position variable. As you can see below, I have the same values being sent to || and CONCAT. CONCAT treats the null as an empty string. Look at the results and see the empty string between hyphens.

CONCAT has some nice advantages, but it also isn't an operator, but a function, so assembling strings feels a little stranger to code.

Summary

This new operator is an interesting edition. It gives you a new way to put strings together that mostly aligns with what the + operator. There are some advantages of the new operator with regards to data type conversions, and it's an ANSI standard operator, so it's worth using.

I felt that the syntax was harder to read and understand at first. I had read Louis Davidson's (@drsql) blog on this operator, and I had to really focus on the code to understand it. However, as I wrote more code and tried to experiment, I actually found it pleasant and easy to use.

This is one of the more interesting changes for me in SQL Server 2025.