The CONCAT_WS function is similar to the CONCAT function. It also concatenates a given expression, but unlike with the CONCAT function, here you can specify a separator. The separator is any string type and the number of characters for the separator is not limited (you can even use more than 8,000 characters).

The following query illustrates the usage of the CONCAT_WS function:

SELECT CONCAT_WS(' / ',name, compatibility_level, collation_name) FROM sys.databases WHERE database_id < 5;

Here is the output returned by the previous query:

 db
-----------------------------------------
master / 140 / SQL_Latin1_General_CP1_CI_AS
tempdb / 140 / SQL_Latin1_General_CP1_CI_AS
model / 140 / SQL_Latin1_General_CP1_CI_AS
msdb / 130 / SQL_Latin1_General_CP1_CI_AS

For the same output with the CONCAT function, you would need to put the separator after each input string:

SELECT CONCAT(name, ' / ', compatibility_level, ' / ', collation_name) AS db FROM sys.databases WHERE database_id < 5;