Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.


Free Beginners SQL Course!

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:

Concatenate using plus operator

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!

Handling NULL with Concatenate

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 
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

CONCAT_WS handles NULL very well

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.

One response to “Concatenate Text with SQL and how to protect from NULL.”
  1. Terry Letkeman

    This is great – thanks so much! I will absolutely be using this in the near future.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Table Of Contents
More from the blog

MySQL PostgreSQL SQLite SQL Server