Introduction
Concatenation
7. The CONCAT_WS() functions
Simple text functions
How to modify Strings
Summary

Instruction

Excellent! Now you know the difference.

When you use CONCAT(), it might be problematic and unreadable to add a space after each column you want to concatenate. Since SQL Server 2017, there is a special function that allows you to enter the separator only once, as a first parameter. Then SQL Server will automatically place that separator between all the joined values in the text. The name of this function is CONCAT_WS().

You can see it in the example below:

SELECT
  CONCAT_WS(N' ', Id, Type, CopywriterId) AS Description
FROM Slogan;
Description
1 tv commercial 3
2 tv commercial 6
...

Exercise

Create the following sentence for each slogan in the Slogan table:

The copywriter of the slogan with ID: X is Y Z

X is the ID number of the slogan; Y and Z are the first and last name of the copywriter. Don't put a period at the end of the sentence. Name the column SloganCopywriter.

Stuck? Here's a hint!

Use the CONCAT_WS() function with the separator set to N' ' and five other arguments.