Month: October 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

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

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

Hope you find this helpful !!!