CONCAT_WS in SQL Server

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.

One thought on “CONCAT_WS in SQL Server”

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