By: Rajendra Gupta | Updated: 2021-02-22 | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Functions System
Problem
Suppose you have a SQL Server table that has customer information such as first name, last name, and email address in separate columns. In the application, you want to display the full name as first name and last name. Usually developers will use a plus (+) sign to concatenate strings, but SQL Server provides useful string functions CONCAT() and CONCAT_WS() for this purpose. In this tutorial, we will explore these functions and cover the differences.
Solution
Usually, we use string data types in SQL Server for text-based data. SQL Server has the following data types for string data:
- char(n): n defines the character string length from 0 to 8000 characters
- varchar(n): n defines the character string length from 0 to 8000 characters
- varchar(max): Maximum storage of 2^31-1 bytes (2 GB)
- nchar(n): Character string length in byte-pairs (0 to 4000 characters)
- nvarchar: Character string length in byte-pairs (0 to 4000 characters)
Let's explore the different methods of data concatenation in SQL Server.
Concat SQL using plus (+) symbol for data concatenation
It is a common practice to use a plus (+) sign as a concatenation operator to concatenate data together.
Syntax for plus(+) concatenation operator
string1 + string2 + ...stringN
For example, in the below SQL query, we concatenate Addressline1 and Addressline2 from the [Person].[Address] table in the [AdventureWorks] database. We are also using a semicolon between Addressline1 and Addressline2 as a separator. Similarly, we can concatenate the City and PostalCode columns.
SELECT TOP 10 AddressLine1, AddressLine2, AddressLine1 + ':' + AddressLine2 as [Address], City, PostalCode, City + ':' + PostalCode as [CityPostCode] FROM [AdventureWorks2017].[Person].[Address] ORDER BY [address]
Looking at the concatenated data below, we can see we have NULL values in the [Address] column while the [CityPostCode] column displays the concatenated data.
If any of the concatenated columns have NULL values, the string concatenation using the plus operator returns NULL or an empty string. In the screenshot below we can see we have a NULL value in the [AddressLine2] column, therefore the string concatenate returns NULL in the [Address] column. On the other hand, both [City] and [PostalCode] don't have NULL values, so we get the correct concatenated single string in the [CityPostCode] column.
Concatenating Data When There Are NULL Values
We could rewrite the query as follows and exclude any rows where [AddressLine2] is null.
SELECT TOP 10
AddressLine1,
AddressLine2,
AddressLine1 + ':' + AddressLine2 as [Address],
City,
PostalCode,
City + ':' + PostalCode as [CityPostCode]
FROM [AdventureWorks2017].[Person].[Address]
WHERE AddressLine2 IS NOT NULL
ORDER BY [address]
The problem with this is we exclude some of the data.
To resolve the NULL values in string concatenation, we can use the ISNULL() function. In the below query, the ISNULL() function checks an individual column and if it is NULL, it replaces it with a space.
SELECT TOP 10
AddressLine1,
AddressLine2,
ISNULL(AddressLine1,' ') + ':' + ISNULL(AddressLine2,' ') as [Address],
City,
PostalCode,
City + ':' + PostalCode as [CityPostCode]
FROM [AdventureWorks2017].[Person].[Address]
ORDER BY [address]
We can verify the output of the data as shown below.
Using CONCAT() function for string concatenation
SQL Server 2012 introduced the CONCAT() function to handle NULL values efficiently while doing concatenation.
CONCAT() function syntax
CONCAT ( string_value1, string_value2 …string_valueN ] )
In the CONCAT() function, we can have a maximum of 254 string_value arguments.
Also, it will also implicitly convert the arguments to string data types before concatenation.
SELECT TOP 10
AddressLine1,
AddressLine2,
CONCAT(AddressLine1,' ',AddressLine2) as [Address],
City,
PostalCode,
CONCAT(City,':',PostalCode) as [CityPostCode]
FROM [AdventureWorks2017].[Person].[Address]
ORDER BY [address]
The CONCAT() function ignores the NULL value. However, it still includes the separator between the columns even if the column has a NULL value.
In the above script, we used a (space) separator and used it in the CONCAT() function to provide space between the AddressLine1 and AddressLine2 columns. We need to specify the separator each time we want to use it. For example, if we want to concatenate multiple strings, we need to specify the separator each time we want in the concatenated string.
Suppose instead of a space, we want to use a dollar sign ($) so the modified query is as below.
SELECT TOP 10
AddressLine1,
AddressLine2,
CONCAT(AddressLine1,'$',AddressLine2) as [Address],
City,
PostalCode,
CONCAT(City,':',PostalCode) as [CityPostCode]
FROM [AdventureWorks2017].[Person].[Address]
ORDER BY [address]
In the output, for the NULL values, we still have a separator. For example, in the first row the output is #500-75 O'Connor Street$.
Similarly, we can look at the concatenated string without NULL values using a dollar sign ($) as a separator.
SELECT TOP 10
AddressLine1,
AddressLine2,
CONCAT(AddressLine1,'$',AddressLine2) as [Address],
City,
PostalCode,
CONCAT(City,':',PostalCode) as [CityPostCode]
FROM [AdventureWorks2017].[Person].[Address]
WHERE AddressLine2 IS NOT NULL
ORDER BY [address]
Using the CONCAT_WS() function for string concatenation
The problem with the CONCAT() function is that we need to specify the separator each time.
In SQL Server 2017 onwards, we have a modified version of the CONCAT() function. This function is CONCAT_WS(), it is also known as concatenate with separator.
CONCAT_WS() function syntax
CONCAT_WS ( separator, argument1, argument2…argumentN)
In this function, we define a separator in the first parameter. The separator is a valid expression of char, nchar, nvarchar, or varchar.
The function uses the separator between each argument. It is similar to the CONCAT function, but you do not need to specify the separator after each argument along with how it handles NULL values.
This function also implicitly converts the arguments to string types before concatenation like the CONCAT function.
It requires a minimum three parameters – one separator and two arguments (values).
Here is the same query, using the CONCAT_WS() function as shown below.
SELECT TOP 10
AddressLine1,
AddressLine2,
CONCAT_WS('$',AddressLine1,AddressLine2) as [Address],
City,
PostalCode,
CONCAT_WS(':',City,PostalCode) as [CityPostCode]
FROM [AdventureWorks2017].[Person].[Address]
ORDER BY [address]
In the output below, we do not see a separator in the [Address] column because the AddressLine2 column has NULL values.
From this example, we can see that the CONCAT_WS() function does not concatenate a separator when there are NULL values.
If we filter records to remove NULL values in the AddressLine2 column, you can see the separator in the concatenated string.
SELECT TOP 10
AddressLine1,
AddressLine2,
CONCAT_WS('$ ',AddressLine1,AddressLine2) as [Address],
City,
PostalCode,
CONCAT_WS(':',City,PostalCode) as [CityPostCode]
FROM [AdventureWorks2017].[Person].[Address]
WHERE AddressLine2 IS NOT NULL
ORDER BY [address]
This function can be useful as a generator for a separated CSV file as well. For example, let's say we require a comma-separated CSV file from the [Person].[Person] table.
SELECT TOP 10 CONCAT_WS(',',FirstName,MiddleName,LastName) as PersonFullName
FROM Person.Person
Execute the SQL query in SQL Server Management Studio and view results in text format. You can then save the results in a file.
One thing to note is that if there are NULL values the columns won't line up as shown above where some rows have 2 commas and others have only 1 comma. To get around this we could also use the ISNULL() function around each of the columns we are selecting as follows.
SELECT TOP 10 CONCAT_WS(',',ISNULL(FirstName,''),ISNULL(MiddleName,''),ISNULL(LastName,'')) as PersonFullName
FROM Person.Person
You can also use a NULL in the separator itself. In case of a NULL separator, SQL Server performs a concatenation without any separator. For example, in the below output, we do not see any separator between [FirstName], [MiddleName] and [LastName].
In case all the parameters supplied are NULL, the CONCAT_WS() function returns an empty string as shown below having a data type of varchar(1).
SELECT CONCAT_WS('_',null, null, null) as OutputString
Use of CONCAT() and CONCAT_WS() in a SQL query
You can use both CONCAT() and CONCAT_WS() functions in a nested SQL query. For example, in the below query, we do the following tasks.
- Use comma (,) as a separator in the CONCAT_WS() function.
- In this function, the first argument of the CONCAT_WS() function is a string concatenation of [AddressLine1] and [AddressLine2] columns. We might have NULL values in the [AddressLine2] column therefore, it is best to use the CONCAT_WS() function.
- The second argument is a string that is data concatenated from the [City] and [PostalCode]. We do not have any records in the table that have NULL in the [City] and [PostalCode] therefore, we can use either CONCAT() or CONCAT_WS() functions. It uses a semicolon (;) as a separator.
SELECT TOP 10 CONCAT_WS(',',
CONCAT_WS(',',AddressLine1,AddressLine2),
CONCAT(city,':',Postalcode)
) as PersonAddress
FROM [AdventureWorks2017].[Person].[Address]
It is preferred to use the CONCAT_WS() function because if someone inserts NULL values, you still get the concatenated string instead of a NULL output.
SELECT TOP 10 CONCAT_WS(',',
CONCAT_WS(',',AddressLine1,AddressLine2),
CONCAT_WS(':',city,Postalcode)
) as PersonAddress
FROM [AdventureWorks2017].[Person].[Address]
Comparison of CONCAT() and CONCAT_WS() functions
- We can specify a separator between the strings in the CONCAT_WS function while the CONCAT() function requires a separator as an argument.
- If we have a NULL parameter, the CONCAT() function returns a NULL output. However, the CONCAT_WS() function ignores the NULL and processes the remaining parameters for data concatenation.
- In the CONCAT() function, you need to specify the separator each time you want to use it between arguments. The CONCAT_WS() requires it a single time, and it automatically adjusts between strings.
Next Steps
- You can go review other string functions.
- Learn about SQL Substring and SQL Server Substring Function Example with T-SQL, R and Python.
- Consider replacing the CONCAT() function with the CONCAT_WS() function for data concatenation without worrying about NULL values in the columns. You can also eliminate additional usage of the ISNULL() function to check NULL values across each column and row.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-02-22
