Learn SQL: Microsoft SQL Server - Episode 10: Joining Multiple Columns and Strings

Sometimes we need to join the data from multiple columns or string values to get the desired result.

Without wasting anytime lets dive into SSMS and see it in practice.

We will be using the Person.Person table. This time however we will be joining the firstName and lastName columns to make a new Full Name column.

To accomplish this we first need to write a SELECT statement to select the firstName and lastName.

Here in our result set we have the firstName and lastName of all the people in our Person.Person table. If we need to join the firstName and lastName to form a Full Name we can do this via two different syntaxes.

Using the Plus sign +

Quite literally we add a plus sign between the firstName and lastName.

firstName + lastName

Let's see what result we get in SSMS.

Did you see? When we added the plus sign it joined the two string values, however it did not add a space in-between. Therefore we needed to add an empty string ' ' by ourselves between the firstName and lastName.

firstName + ' ' + lastName

Finally we added an Alias to make things more simple to understand.

You have to use the plus sign syntax if you are using older versions of SSMS. For newer versions after SSMS 2008, use the Concat syntax. The plus syntax works in all versions thus far.

Using the Concat function

We can also use what is called the Concat function, similar to other functions, we wrap our arguments inside the function. In this case we wrap the firstName, the empty string, and the lastName inside our Concat function.

Concat(firstName, ' ', lastName)

We can add a many arguments or columns/strings inside the Concat function as we please. However notice that all of them have to be separated by a comma.

When we run these two syntaxes side by side, we can see they produce the exact same result. Now we have two ways of combining data from columns and string values.

Pretty cool stuff! I really enjoyed this one.