This article explores SQL Server Concatenate operations using the SQL Plus (+) operator and SQL CONCAT function.
Introduction
We use various data types in SQL Server to define data in a particular column appropriately. We might have requirements to concatenate data from multiple columns into a string. For example, in an Employee table, we might have first, middle and last name of an employee in different columns.
In the following screenshot, we have top 10 records from an employee table whose middle name is NOT NULL (I will explain the reason for it as we move on in this article)
|
1 2 3 4 5 |
SELECT TOP (10) [FirstName], [MiddleName], [LastName] FROM [AdventureWorks2017].[Person].[Person] where Middlename IS NOT NULL |
SQL Plus Operator Overview
Usually, we use a SQL Plus (+) operator to perform SQL Server Concatenate operation with multiple fields together. We can specify space character as well in between these columns.
Syntax of SQL Plus(+) operator
string1 + string2 + …….stringn
Examples of SQL Plus(+) Operator
Look at the following query. In this query, we use SQL Plus(+) operator and space between the single quote as a separator between these fields.
|
1 2 3 4 5 6 |
SELECT TOP 10 FirstName, MiddleName, LastName, FirstName + ' ' + MiddleName + ' ' + LastName AS FullName FROM [AdventureWorks2017].[Person].[Person] WHERE Middlename IS NOT NULL; |
In the output of SQL Server Concatenate using SQL Plus (+) operator, we have concatenate data from these fields (firstname, MiddleName and LastName) as a new column FullName.
We have a drawback in SQL Server Concatenate data with SQL Plus(+) operator. Look at the following example.
|
1 2 3 4 5 |
SELECT TOP 10 FirstName, MiddleName, LastName, FirstName + ' ' + MiddleName + ' ' + LastName AS FullName FROM [AdventureWorks2017].[Person].[Person] |
In this example, we can see that if we have any NULL value present in any fields, we get output of concatenate string as NULL with SQL Plus(+) operator.
We can use SQL ISNULL function with + operator to replace NULL values with a space or any specific value. Execute the following query and we can still use SQL Plus(+) operator to concatenate string for us.
|
1 2 3 4 5 |
SELECT TOP 10 FirstName, MiddleName, LastName, ISNULL(FirstName, '') + ' ' + ISNULL(MiddleName, '') + ' ' + ISNULL(LastName, '') AS FullName FROM [AdventureWorks2017].[Person].[Person]; |
Let’s look at another example of SQL Server Concatenate values with string as well as numeric values. In the following query, we want to concatenate first, middle, full name along with the NationalID number.
|
1 2 3 4 5 6 7 8 |
SELECT p.[Title], p.[FirstName], p.[MiddleName], p.[LastName], p.FirstName + ' ' + p.MiddleName + ' ' + p.LastName + 'NationalIDNumber is :' + e.NationalIDNumber AS EmpDetail FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID] WHERE p.Middlename IS NOT NULL; |
Suppose we want the single quote as well in the SQL Server Concatenate. In SQL Server, once we combine strings using SQL Plus(+) operator, it concatenates values inside single quotes. In the following query, we can see we specified two single quotes to display a single quote in the output.
|
1 |
SELECT 'Let''s' + ' explore SQL Server with articles on SQLShack'; |
If there is any mismatch or incorrect use of the single quote, you get following error message.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘ + ‘.
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression,
an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ‘;
‘.
If we want to print more single quotes, we need to define in the string in the following format with two single quotes.
Expected Output: ‘Let’s explore SQL Server with articles on SQLShack’
We can modify SQL query as follow.
|
1 |
SELECT '''Let''s' + ' explore SQL Server with articles on SQLShack'''; |
- Note: You should not confuse a single quote with the double quotes. SQL Server treats double quotes as a character.
In the following query, we used a double quote in a combination of a single quote.
|
1 |
SELECT '"''Let''s' + ' explore SQL Server with articles on SQLShack'''; |
We can do SQL Server Concatenate operation using the SQL Plus(+) operator; however, it becomes complex if you need to use multiple single quotes. It is difficult to debug code as well since you need to look at all the single quotes combinations as well.
SQL CONCAT FUNCTION
Starting from SQL Server 2012, we have a new function to concatenate strings in SQL Server.
Syntax of SQL CONCAT function
CONCAT ( string1, string2….stringN)
We require at least two values to concatenate together and specify in the SQL CONCAT function.
Examples
Let’s explore SQL CONCAT with an example. The following query, concatenate string and gives output as a single string. We specified multiple single quotes between each word to print space in between each word.
|
1 |
SELECT CONCAT( 'My',' ', 'Name',' ', 'is',' ', 'Rajendra',' ', 'Gupta') AS introduction |
We can use system functions as well in concatenate sting using SQL CONCAT function.
|
1 |
SELECT CONCAT( 'SQL ISNULL function published on',' ',GETDATE()-2) AS SingleString |
We use Getdate() function to get a specified date in a concatenated string as well.
In the previous section, we explored that if we want to concatenate string using + operator and any of string has a NULL value, and we get the output as NULL. We use SQL ISNULL function to replace NULL values in the string. We need to use SQL ISNULL with each column containing NULL values. If we have a large number of the column that may have NULL values, it becomes complex to write such code.
Let’s review this again with SQL CONCAT function. We only need to specify the SQL CONCAT function at once and specify all string columns. We get the output as a concatenated string.
|
1 2 3 4 5 |
SELECT TOP 10 FirstName, MiddleName, LastName, CONCAT(FirstName, ' ',MiddleName,' ',LastName) AS FullName FROM [AdventureWorks2017].[Person].[Person]; |
- Note: If all the string passed in SQL CONCAT function have a NULL value, we get the output of this function also NULL.
SQL CONCAT and data type conversion
SQL CONCAT function implicitly converts arguments to string types before concatenation. We can use SQL CONVERT function as well without converting the appropriate data type.
If we concatenate string using the plus( +) operator, we need to use SQL CONVERT function to convert data types. Lets’ look this using the following example.
In the following query, we want to concatenate two strings. In this example, data type of first string is Text while another data type is a date.
|
1 2 3 |
Declare @Text Varchar(100)='Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on' Declare @date date='20190422' Select @Text +' '+ @date as Result |
Execute this query, and we get the following output.
Msg 402, Level 16, State 1, Line 3
The data types varchar and date are incompatible in the add operator.
We need to use SQL CONVERT function as per the following query, and it returns output without error message.
|
1 2 3 |
Declare @Text Varchar(100)='Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on' Declare @date date='20190422' Select @Text+' '+Convert (varchar,(@date)) as Result |
We do not need to use SQL CONVERT function to convert data type in SQL CONCAT function. It automatically does the conversion based on the input data type.
|
1 2 3 |
Declare @Text Varchar(100)='Microsoft SQL Server 2019 Community Technology Preview 2.5 launched on' Declare @date date='20190422' Select concat(@Text,@date) as Result |
In the following table, we can see that data type conversion for input and output data types in SQL CONCAT function.
Input Data Type | Output Data Type |
SQL CLR | NVARCHAR(MAX) |
NVARCHAR(MAX) | NVARCHAR(MAX) |
NVARCHAR(<=4000 characters) | NVARCHAR(<=4000 characters) |
VARBINARY(MAX) | NVARCHAR(MAX) |
All other data types | VARCHAR(<=8000) *if any parameters data type is NVARCHAR, the output value will be NVARCHAR(MAX) |
Conclusion
In this article, we explored useful SQL functions to concatenate multiple values together using the SQL Plus(+) operator and SQL CONCAT function. If you had comments or questions, feel free to leave them in the comments below.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023