In this article I’ll show you three ways you can use SQL to concatenate columns into a single expression. You’ll see as we go through this exercise that CONCAT_WS comes out on top for handling NULL the best!
The examples are based on the PizzaDB, git it here!
The goal is to combine the FirstName and LastName from the employee table. Here is a simple listing:
select * from employee
The first method you can use to concatenate columns is to use the + operator. It simply combines text values together. This query
select FirstName + ' ' + LastName as FullName from Employee
returns the following result:
You can easily see how the first and last names are separated by a space.
The concatenate second method uses the built-in CONCAT function to do the same.
select concat(FirstName, ' ', LastName) as FullName from Employee
It strings together every argument together. In my opinion, It is easier to read.
The last concatenate method I’ll cover is CONCAT_WS. Here we specify the separator to use to between every column. The example query becomes:
select concat_ws(' ',FirstName, LastName) from Employee
What I like about this function is that is becomes very easy to ad more columns. They just become additional parameters. No need to add more separators or get into adding more pluses and so on.
Concatenate and NULL
But where CONCAT_WS really shines is when handling NULL.
Consider the following query:
select FirstName + ' ' + LastName + ' ' + null + ' ' + 'A' as FullName from Employee
It returns all NULL rows! Not cool!
To protect from nulls we need to write the following:
select case when FirstName is null then '' else FirstName + ' ' end + case when LastName is null then '' else LastName + ' ' end + case when null is null then '' else ' ' end + case when 'A' is null then '' else 'A' end FullName from Employee
The case statements complicate the query. Fortunately CONCATE_WS handles all of this automatically for us. Check out this query:
select concat_ws(' ',FirstName, LastName, null, 'A') from Employee
What I like about CONCAT_WS, is the built-in function automatically handles the NULL so that you don’t have to do extra coding. Clearly, CONCAT_WS is a good choice to use.