Concatenating strings in T-SQL

It may look like an easy task to concatenate string in T-SQL at the first look. But it still has some hidden points where beginners are failing most of the time. There are a few very important things to remember when working with string and doing concatenation. Without respecting them the concatenation of mainly longer strings for dynamic SQL purposes will fail.

These are magic key points to remember:

• The maximum length for constants is limited to 4000 characters.
• The + (plus) operator works from left to right.
• It exists something like implicit data type conversion.

Let’s check the following example:

DECLARE @SQL1 NVARCHAR(MAX) 
DECLARE @SQL2 NVARCHAR(200) 

-- use this to get 4000 characters long string and assign it to SQL1
	-- SELECT REPLICATE('X', 4000)

SET @SQL2 = '12345' 
SET @SQL1 = 'XXXXX....' + @SQL2 

SELECT LEN(@SQL1) 
GO

We have defined two variables. In SQL2 we have assigned 5 characters long string. Then we are trying to build the SQL1 string using 4000 characters long string prepared with the REPLICATE() function + string stored in the SQL2 variable. We would like to expect the length of SQL1 to be 4005 characters. But it shows a length of only 4000 characters finally. What’s going wrong? It’s caused by the fact that SQL Server is doing concatenation of 4000 characters long constant and 200 characters long string data type so the final result has been evaluated to be 4000 characters long string applying rules mentioned above.

We can fix that easy by changing the SQL2 variable to be NVARCHAR(MAX) instead of NVARCHAR(200):

DECLARE @SQL1 NVARCHAR(MAX)
DECLARE @SQL2 NVARCHAR(MAX)

-- use this to get 4000 characters long string and assign it to SQL1 
	-- SELECT REPLICATE('X', 4000)

SET @SQL2 = '12345'

SET @SQL1 = 'XXXXX....' + @SQL2

SELECT LEN(@SQL1)
GO

For better understanding we can also try this combination of variables:

DECLARE @SQL1 NVARCHAR(MAX)
DECLARE @SQL2 NVARCHAR(200)
DECLARE @SQL3 NVARCHAR(MAX)

-- use this to get 4000 characters long string and assign it to SQL1 
	-- SELECT REPLICATE('X', 4000)

SET @SQL2 = '12345'
SET @SQL3 = '67890'

SET @SQL1 = 'XXXXX...' + @SQL2 + @SQL3

SELECT LEN(@SQL1) StringLen
SELECT RIGHT(@SQL1, 20)
GO

It’s showing how SQL server is processing the expression from left to right. It concatenated 4000 characters long constant with the NVARCHAR(200) string where (as per rules above) the result can’t be longer than 4000 characters because of implicit data type conversion applied. Next, it added the SQL3 variable with NVARCHAR(MAX) data type so the result can be longer than 4000 characters. The value of SQL2 variable was completely eliminated from the final string. We can get to the expected result the same way as before: just change the SQL2 variable to be NVARCHAR(MAX).

There is one trick you can use to override the default behavior of constants handling: simply cast them to NVARCHAR(MAX):

DECLARE @SQL1 NVARCHAR(MAX)
DECLARE @SQL2 NVARCHAR(200)
DECLARE @SQL3 NVARCHAR(MAX)

-- use this to get 4000 characters long string and assign it to SQL1 
	-- SELECT REPLICATE('X', 4000)

SET @SQL2 = '12345'
SET @SQL3 = '67890'

SET @SQL1 = CAST('XXXXX...' AS NVARCHAR(MAX)) + @SQL2 + @SQL3

SELECT LEN(@SQL1) StringLen
SELECT RIGHT(@SQL1, 20)
GO

Two general recommendations for working with string in (dynamic) T-SQL can be based on examples presented above :

• declare string variables as NVARCHAR(MAX) only
• cast all string constants to NVARCHAR(MAX)

Some other variants of string concatenation can be found in this blog.