How to Concatenate String and NULL Values in SQL Server

Problem:

You’d like to concatenate text columns in SQL Server, when some of the columns contain NULL.

Example:

Our database has a table named children with data in the following columns: id, first_name, middle_name, and last_name.

We want to display the first name from one column, the middle name from the next column, and the last name from the last column as one string, even if one of the columns stores a NULL.

Solution 1:

We’ll use the CONCAT() function. Here’s the query you’d write:

SELECT 
  CONCAT(first_name,  middle_name, last_name) AS name
FROM children;

Here is the result:

Discussion:

In SQL Server, you can use the CONCAT() function to concatenate string values from expressions or columns containing a NULL. This function takes a list of strings and displays all these values in one string. The function ignores NULL, they are treated as if they were an empty string.

Solution 2:

Note that there is no separator between the values, so the results (as in our example) may not be formatted as you’d expect. How can we correct this? Look at another CONCAT() query:

Here’s another query option:

SELECT 
  CONCAT(first_name, ' ', middle_name, ' ', last_name) AS name
FROM children;

Here is the result:

Now, apart from string values, this function also takes some spaces (placed between the concatenated values). This separates one part of the name from another. But as we see, this solution is also not ideal; full names without middle names have an extra space, while the records with only one name have two extra spaces.

In this case, the CONCAT_WS() function is the better solution.

Solution 3:

The CONCAT_WS() function takes an additional first argument: a character that serves as a separator between strings. Here’s the query:

SELECT 
  CONCAT_WS(' ' , first_name, middle_name, last_name) AS name
FROM children;

And the result:

This query displays the children’s full names, without any unneeded spaces.