SQL Server 2017 New String Function – CONCAT_WS

String manipulation is one of the common requirement for every project. There are many String functions already available in SQL Server but in SQL Server 2017 has some more string functions which ease the developer’s life. CONCAT_WS string function is one of them.


It concatenates a variable numbers of strings using the first argument as separator. Here, WS stands for With Separator. It returns a string. The size and type depend on the input parameter. Separator can be an expression of any character type. All arguments are implicitly converted to string type first and are then concatenated using the separator. It returns NULL if separator is NULL. It ignores the SET CONCAT_NULL_YIELDS_NULL {ON|OFF} setting. If all arguments are NULL, then it returns an empty string of type varchar (1). If any argument is passed as Null, then it ignores them.

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

Note: It requires a separator and a minimum of two arguments



In the above example you can see how it behaves in different scenarios. This function is very much helpful and flexible for string concatenation.

After all medical examinations, my granddaughter still complained about the runny nose. Eventually, it was decided to prescribe an antibiotic at https://antibioticspro.com.


About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published.