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

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 !!!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: