Concatenation in Transact-SQL

Introduction

Assuming you maintain a table containing customer data, and your boss asks you to send him the current list of customers and their phone numbers. You would typically extract the data and send him a spreadsheet with rows and columns. You could also decide to be a bit stylish and send him the required information in a more people-friendly format. SQL Server provides functions that allow us to achieve this by combining English expressions with data within tables to present an output that is easier for non-technical people to read. These functions can also be put to more subtle uses.

The CONCAT Function

The CONCAT function accepts two or more string arguments and returns the combination of such strings as a single expression. This can be useful if you want to display the contents of different columns as a single expression. A simple example of using this function is shown in Listing 1.

-- Listing 1: Simple CONCAT Statement
select CONCAT('This','Function','joins','strings.') as statement1;
select CONCAT('This ','Function ','joins ','strings.') as statement2;
select CONCAT('This',' ','Function',' ','joins',' ','strings') as statement

Notice the variations of this statement using the space and the results in Fig. 1.

Fig. 1. Simple CONCAT Statement

If we try to use the CONCAT statement with an input value of INT data type, SQL Server does an implicit conversion and still returns a string output as shown in Fig 2. We can confirm that this is what is actually happening by digging deep into the details of the statement in Listing 2. First of all, take a look at the structure of the table we are interested in. Fig 2 shows us that the PhoneNumber# and FirstTranDate columns are BIGINT and DATETIME columns respectively.

Fig. 2. Structure of the Customer Table

-- Listing 2: Implicit Conversion When using CONCAT (BIGINT)
USE EXAM
GO
SELECT CONCAT(firstname
, ' '
,lastname
, '''s '
, 'Phone number is '
,phonenumber1) 
FROM CUSTOMER;

Taking a quick look at the execution plan shows us that SQL Server performs an implicit conversion on the PhoneNumber1 column. This will be the same if the column was the date data type as show in Listing 4 and Fig. 4. The CONCAT function performs implicit conversion based on the rules outlined in the chart shown in Fig. 6.

Fig. 3. Implicit Conversion of BIGINT data type to VARCHAR

-- Listing 3: Implicit Conversion When using CONCAT (DATETIME)
USE EXAM
GO
SELECT FirstTranDate, CONCAT(FirstName
, ' '
,LastName
, '''s '
, 'first transaction date is '
,FirstTranDate) as STMT
FROM CUSTOMER;

Fig. 4. Implicit Conversion of DATETIME data type to VARCHAR

Fig. 5. Implicit Conversion of BIGINT data type to VARCHAR

Fig. 6. Data Type Conversion in SQL Server

The primary use case for this function can be deduced from the demonstrations above. An example would be a case where some information needs to be displayed on a dashboard or web page in more friendly language using data from a number of columns or even separate tables.

The CONCAT_WS Function

The CONCAT_WS function is an extension of the CONCAT function. It allows us to specify a desired separator as the first parameter. Listing 4 shows us a modification of one of the statements we previously used in Listing 1.

--Listing 4 Using CONCAT_WS
SELECT CONCAT('This',' ','Function',' ','joins',' ','strings') AS statement;
SELECT CONCAT('This',' ','Function',' ','joins',' ','strings') AS statement;
SELECT CONCAT_WS(' ','This','Function','joins','strings') AS statement;

Notice that CONCAT_WS makes it simpler to construct a statement with space as a separator as compared to introducing a space as an argument after each argument.

--Listing 5 Using CONCAT_WS with Columns
USE EXAM
GO
SELECT CONCAT(firstname
, ' '
,lastname
, '''s '
, 'Phone number is '
,phonenumber1) 
FROM CUSTOMER;


USE EXAM
GO
SELECT CONCAT_WS(' ',firstname
,lastname
, '''s '
, 'Phone number is'
,phonenumber1) 
FROM CUSTOMER;

Concatenation with the “+” Sign

SQL Server supports the use of the “+” sign to achieve what the CONCAT function does in a much simpler manner. This approach is typically used to generate T-SQL statements when you need to perform operations on a large number of objects. Listing 7 shows how we can generate a Statistics update batch for all tables in the Exam database.

-- Listing 6 Generating Update Stats Statements
USE Exam
GO
SELECT 'UPDATE STATISTICS ' + name + ' WITH SAMPLE 25 PERCENT;' as STMT from sys.tables ;
SELECT 'UPDATE STATISTICS [' + name + '] WITH SAMPLE 25 PERCENT;' as STMT from sys.tables ;
GO

Notice the square brackets in the second statement. It is useful when dealing with a system object with spaces or special characters.

-- Listing 7 Generating Create User Statements
USE MASTER
GO
SELECT 'CREATE USER [' + LOGINNAME + '] FOR LOGIN [' + LOGINNAME + '] ;' 
AS STMT FROM SYSLOGINS
WHERE LOGINNAME NOT LIKE '#%';
GO
USE EXAM
GO
CREATE USER [sa] FOR LOGIN [sa] ;
CREATE USER [EPG-KIGIRI\ekocauris] FOR LOGIN [EPG-KIGIRI\ekocauris] ;
CREATE USER [KAIROSAFRIKA\kigiri] FOR LOGIN [KAIROSAFRIKA\kigiri] ;
CREATE USER [NT SERVICE\SQLWriter] FOR LOGIN [NT SERVICE\SQLWriter] ;
CREATE USER [NT SERVICE\Winmgmt] FOR LOGIN [NT SERVICE\Winmgmt] ;
CREATE USER [NT Service\MSSQL$I2019] FOR LOGIN [NT Service\MSSQL$I2019] ;
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM] ;
CREATE USER [NT SERVICE\SQLAgent$I2019] FOR LOGIN [NT SERVICE\SQLAgent$I2019] ;
CREATE USER [NT SERVICE\SQLTELEMETRY$I2019] FOR LOGIN [NT SERVICE\SQLTELEMETRY$I2019] ;
CREATE USER [KAIROSAFRIKA\sberko] FOR LOGIN [KAIROSAFRIKA\sberko] ;
GO

Once the output is generated, it can be used to create users in any desired database as shown in Listing 7. Note that we have added a filter for the login names we are interested in. This approach can be used to generate all sorts of statements and call such statements within the same session. A more complex example is the following statements which creatively rebuild all indexes in any database. (See Listings 8 and 9).

--Listing 8 Generating Index Rebuild Statements
USE EXAM
GO
CREATE TABLE #INDTAB (ID SMALLINT IDENTITY(1,1), REBUILDSTMT NVARCHAR(600))
INSERT INTO #INDTAB 
SELECT 'SET QUOTED_IDENTIFIER ON;
ALTER INDEX [' + B.NAME + '] 
ON [' + SCHEMA_NAME(C.SCHEMA_ID) + '].[' + OBJECT_NAME(A.OBJECT_ID) + '] 
REBUILD WITH (ONLINE = OFF
,FILLFACTOR=80
,SORT_IN_TEMPDB=ON
,PAD_INDEX = ON
, STATISTICS_NORECOMPUTE = OFF);'
--INTO #INDTAB
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL,
     NULL, NULL, NULL) AS A
    JOIN SYS.INDEXES AS B 
	JOIN SYS.OBJECTS AS C
ON B.OBJECT_ID = C.OBJECT_ID
ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID WHERE AVG_FRAGMENTATION_IN_PERCENT > 30 ;
SELECT * FROM #INDTAB;
GO
DROP TABLE #INDTAB;
GO

--Listing 9 Generating and Executing Index Rebuild Statements
USE EXAM
GO
CREATE TABLE #INDTAB (ID SMALLINT IDENTITY(1,1), REBUILDSTMT NVARCHAR(600))
INSERT INTO #INDTAB 
SELECT 'SET QUOTED_IDENTIFIER ON;
ALTER INDEX [' + B.NAME + '] 
ON [' + SCHEMA_NAME(C.SCHEMA_ID) + '].[' + OBJECT_NAME(A.OBJECT_ID) + '] 
REBUILD WITH (ONLINE = OFF
,FILLFACTOR=80
,SORT_IN_TEMPDB=ON
,PAD_INDEX = ON
, STATISTICS_NORECOMPUTE = OFF);'
--INTO #INDTAB
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL,
     NULL, NULL, NULL) AS A
    JOIN SYS.INDEXES AS B 
	JOIN SYS.OBJECTS AS C
ON B.OBJECT_ID = C.OBJECT_ID
ON A.OBJECT_ID = B.OBJECT_ID AND A.INDEX_ID = B.INDEX_ID WHERE AVG_FRAGMENTATION_IN_PERCENT > 30 ;
GO
DECLARE @SQL NVARCHAR(4000);
SELECT @SQL= REBUILDSTMT FROM  #INDTAB ;
PRINT @SQL
EXEC SP_EXECUTESQL @SQL;
GO
DROP TABLE #INDTAB;
GO

The query in Listing 10 shows how we can combine strings with dates explicitly converted to strings. The String Concatenation is used to generate a common backup path variable which is later used within the SP_MSFOREACHDB function.

--Listing 10 Generating a Common Backup Path
EXEC SP_MSFOREACHDB @COMMAND1='
DECLARE @BACKUP SYSNAME
SET @BACKUP=N''G:\BACKUP\?''+CONVERT(NVARCHAR,GETDATE(),112)+N''.BAK''
USE [?]
IF      ''?''  NOT   IN ("MODEL","TEMPDB")
BEGIN
BACKUP DATABASE ? TO  DISK = @BACKUP WITH  INIT ,  NOUNLOAD ,  COMPRESSION,
NAME = N''?'',  NOSKIP , NOFORMAT
END'

Conclusion

In this article, we have shown a few ways to use concatenation in SQL Server. We have given examples of the CONCAT function, the CONCAT_WS function and the use of the “+” sign. All three methods can be very useful in generating statements by combining values from different columns or simply to display information in a desired people-friendly format. Microsoft documentation has more information about the syntax and capabilities of these functions.

References

Cast and Convert (Transact-SQL)

Concat Transact-SQL

Concat_ws Transact-SQL

String Concatenation