CONCAT_WS is a new function introduced from SQL Server 2017. CONCAT_WS denotes concatenate the strings with a separator. The syntax of CONCAT_WS is as below:

CONCAT_WS(separator,string1,string2.....stringN)

Prior to SQL Server 2017, we will have to use “+” operator to concatenate and carefully place the separator, that really adds lots of frustration to any developer (At least, I hate this in my initial coding days). CONCAT_WS is really a good winner in such use cases. In one of my use case, we wanted to send data from one table to external system, which works on BizTalk, as a pipe separated text file format. Earlier, we had to use carefully do lots of checks at bot SQL Server and BizTalk sides to avoid data issues like NULL handling etc. This function reduces a lots of work in such cases.

Few Points to be considered:

1. This function skips NULL value. That means we will have to explicitly handle NULL value columns if the column needs to be considered in the output.
2. The maximum number of parameters allowed is 254.
3. This function implicitly converts all arguments to string type before concatenation.
4. This function requires minimum a separator parameter and at least other two string parameters.