How To Use CONCAT_WS Function In SQL Server

CONCAT_WS, a string Concatenation function With Separator (WS). A fresh addition to SQL Server 2017. CONCAT_WS function takes multiple string arguments (at least two) and separator as an input. Furthermore, joins multiple strings together into a single string – connected by a Separator. While CONCAT_WS is an extension to CONCAT function with an added separator.

First of all, with CONCAT function we need to specify the separator multiple times while concatenating columns. Whereas CONCAT_WS function simplifies this limitation by letting us specifying the separator only once.

Syntax

CONCAT_WS(Separator, String1, String2, String3, ..., StringN)

Separator – A single or multi-character string type parameter.

String1, String2, …. String N – Parameter of any type. The function will do an implicit conversion of any data type of parameter to string before concatenation.

Return output is of string data type and output length is based on input strings.

CONCAT_WS – Points To Ponder

1.Function combines multiple strings together With Separator.

2.Concatenating columns of any data type is accepted. The function will convert input data type implicitly to string data type before concatenation.

3.If all input strings are NULL then it will return an EMPTY string.

4.CONCAT_WS works only with SQL Server 2017 and with compatibility level 140 and above.

5.In comparison to SQL Server – Oracle and MySQL RDBMS supports CONCAT_WS function.

CONCAT_WS – Examples

Let’s demonstrate CONCAT_WS function with significant examples. Before running these queries, be sure that you are on SQL Server 2017 version with compatibility level 140 or higher.

1.

CONCAT_WS – Concat String Inputs With Separator

This query uses . (dot) as a separator and joins input strings together. Note that . a separator is added in between.

CONCAT_WS is an extension to CONCAT avoiding the use of separator multiple times.

--CONCAT_WS - Specify the Seperator Only Once
SELECT CONCAT_WS('.','www','tech-recipes','com') as ConcatWS;

--Using CONCAT function to achieve same results
SELECT CONCAT('www','.','tech-recipes','.','com') as [Concat];

2.

CONCAT_WS – Concat Different Data Type Inputs With Separator

This example demonstrates that CONCAT_WS function will implicitly convert input data types to string data type before concatenation and will produce string typed result as an output.

Using Decimal, varchar, char, Integer data type as arguments and producing a string as output. For instance, we can specify multi-character separator ## as in this example.

--Multiple data types concat together (Implicit Convert)

DECLARE @Integer as INT          = 10
       ,@Char     as CHAR(1)     = 'A'
       ,@Vchar   as VARCHAR(4)   = 'Test'
       ,@Dmal    as Decimal(9,2) = 6543.21;

SELECT CONCAT_WS('##', @Integer, @Char, @Vchar, @Dmal) as ConcatWS;

3.

CONCAT_WS – All NULL values as Parameter.

Particularly, this example demonstrates the case if all input parameters are NULLs. CONCAT_WS ignore NULLs and produces an EMPTY string. Compared to CONCAT function which ignores NULLs but returns Separator as in below query.

--Ignores NULLs and Return EMPTY String
SELECT CONCAT_WS('-', NULL, NULL, NULL) as ConcatWs;

--Ignores NULLs but keeps Seperator
SELECT CONCAT(NULL,'-',NULL,'-',NULL) as [Concat];

4.

CONCAT_WS – Ignore NULLs

In this example, we have taken multiple arguments – combining NOT NULLs and NULLs to see the behaviour of CONCAT_WS. Accordingly, CONCAT_WS ignores NULLs and joins the NOT NULLs together.

Trying to implement similar functionality using CONCAT function yields a different result as it joins separator even though there was a NULL value. Therefore, additional code is needed to remove hyphens when NULLs are present.

--NULL ignored and Hypen Adjusted
SELECT CONCAT_WS('-','Hello',NULL,'There',NULL,'Howdy');

--NULL ignored but Hypen is not Adjusted
SELECT CONCAT('Hello','-', NULL,'-', 'There','-', NULL,'-', 'Howdy');

5.

CONCAT_WS – Concat Columns in Table

In the following example, we are using the Employee table and combining all the available columns separated by single space. CONCAT_WS function handle NULLs internally and also implicitly converts INT data type to string. Produces a combination of four columns.

Besides, we also generated comma-separated values (CSV). Replacing NULLs with N/A string.

use TEMPDB;
Go

DECLARE @Employee TABLE
(
  EmpId			INT
 ,FirstName		VARCHAR(50)
 ,MiddleName	        VARCHAR(50)
 ,LastName		VARCHAR(50)
 );

 INSERT INTO @Employee VALUES
  (100, 'Shaun','K','Malik')
 ,(200, 'Ravin',NULL,'Dpak')
 ,(300, 'Venkat', NULL, NULL);
 
 --Adding Single Space Seperator and Implict Conversion of INT
 --Data type to String

 SELECT CONCAT_WS(' ',EmpId, FirstName, MiddleName, LastName)
 FROM   @Employee;

 --Generating Comma seperated values and handling NULL to be replaced by N/A
  SELECT CONCAT_WS(',',EmpId, ISNULL(FirstName,'N/A'), ISNULL(MiddleName,'N/A'), ISNULL(LastName,'N/A'))
 FROM   @Employee;

Summary

Firstly we have learned CONCAT_WS function’s syntax. Plus we understood how CONCAT_WS is different from CONCAT function. Using pratical examples on CONCAT_WS function. If you like this post you may like Tech-recipes database articles.