More Fun with Smushing Data Together

Last week, we learned all about the old-style string concatenation and the new-ish, hip CONCAT function.

This week, it’s time to talk about the CONCATs lesser talked about, but equally helpful cousin – CONCAT_WS.

With CONCAT, we formatted the person’s name in a LastName, FirstName format using the following query.

SELECT CONCAT(TRIM(LastName), ', ', TRIM(FirstName))
FROM dbo.Person1

We can do the same thing using CONCAT_WS as shown below:

SELECT CONCAT_WS(', ',  TRIM(LastName), TRIM(FirstName))
FROM dbo.Person1

Now, this doesn’t make anything any easier or even make the process faster for only 1 separator.

But, if I had to concatenate 10, 15, or even 30 columns together all of them separated by the same character – in this case “,” – then life is golden!

For example, we could have created the following query which smooshes so many of our columns together.

SELECT CONCAT_WS(', ', p1.FirstName, mpd.FirstName, p1.LastName, mpd.LastName, p1.Email, mpd.Email, p1.Gender, mpd.Gender, p1.ZipCode)
FROM dbo.Person1 p1
JOIN dbo.MockPersonData mpd ON mpd.ID = p1.ID 

This forces the comma and 1 space between each of the fields which is extremely consistent. Especially over time, I have noticed that many developers will not keep the comma and 1 space consistent if they had to list all of the data out with either the CONCAT function or the + operator.

With that, I leave you with a new fun function that you should now be able to use at your convenience. Until next time!