Rajendra Gupta
SQL Server CONCATENATE

SQL Server CONCATENATE Operations with SQL Plus (+) and SQL CONCAT functions

May 13, 2019 by

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)

SQL Server CONCATENATE

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.

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.

SQL Server CONCATENATE

We have a drawback in SQL Server Concatenate data with SQL Plus(+) operator. Look at the following example.

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.

SQL Server CONCATENATE

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.

Example of SQL CONCATENATE

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.

Example of SQL CONCATENATE

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.

Example of SQL CONCATENATE

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.

Example of SQL CONCATENATE

  • 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.

Example of SQL CONCATENATE

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.

SQL CONCAT

We can use system functions as well in concatenate sting using SQL CONCAT function.

We use Getdate() function to get a specified date in a concatenated string as well.

SQL CONCAT

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.

SQL CONCAT example

  • 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.

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.

CONCAT examples

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.

Concat examples

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
91 Views