CONCAT_WS() – Concatenate with Separator–New String Function in SQL Server 2017


SQL Server 2017 has a new String function which supports concatenating multiple values with separator, ex. Comma (,) or Pipe (|)

This function will come handy, if you want to generate a CSV file quickly

Syntax for the function is as follows

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

Example Usage

CREATE TABLE [dbo].[Test](
[intPK] [int] IDENTITY(1,1) NOT NULL,
[strData] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[intPK] ASC
)
)
GO

INSERT INTO dbo.test (strData) VALUES (‘Test1’)
INSERT INTO dbo.test (strData) VALUES (‘Test2’)
INSERT INTO dbo.test (strData) VALUES (‘Test3’)
INSERT INTO dbo.test (strData) VALUES (‘Test4’)
INSERT INTO dbo.test (strData) VALUES (‘Test5’)

SELECT CONCAT_WS(‘|’, intPK, strData) AS Result FROM dbo.Test

In above example we are concatenating 2 fields using pipe as separator

Output

capture20171003120119644

NULL Handling

If any of the data fields has NULL, then we need to use ISNULL() or COALESCE() to handle this, else, the structure of the output will change

ALTER TABLE dbo.Test ADD strNullData VARCHAR(50) NULL

UPDATE dbo.Test SET strNullData=’A’ WHERE intPK=1
UPDATE dbo.Test SET strNullData=’B’ WHERE intPK=3
UPDATE dbo.Test SET strNullData=’C’ WHERE intPK=5

SELECT CONCAT_WS(‘|’, intPK, strNullData, strData) Result FROM dbo.Test

In the below output, rows 1,3 and 5 has 3 fields and rows 2 and 4 has only 2 fields

capture20171003125644345

Query with ISNULL() Handling

SELECT CONCAT_WS(‘|’, intPK, ISNULL(strNullData, ”), strData) Result FROM dbo.Test

Now the below output will have consistent list of fields

capture20171003125845846

Hope you find this helpful !!!

Author: Arunraj

I am a Microsoft Certified Technology Specialist (Database Developer). I work on SQL Server programming since SQL Server 7.0 specializes in SQL Server Programming and Performance Tuning and has 14 years of hands-on experience. I hold a Master Degree in Computer Applications. I am also part of NJSQL User Group and Northern New Jersey .Net User Group.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s