Concatenate Text with SQL and how to protect from NULL.

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.