Articles Cookbook
Back to list
SQL Server

How to Concatenate String and NULL Values in SQL Server

Database:

Operators:

CONCAT(),CONCAT_WS()

Problem:

You’d like to concatenate NULLs with string values from a different column in SQL Server.

Example:

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

idfirst_namemiddle_namelast_name
1LindaNULLJackson
2MaryAliceThomson
3NULLStevenNULL
4NULLNULLBrown

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

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:

name
LindaJackson
MaryAliceThomson
Steven
Brown

Discussion:

Use the CONCAT() function to concatenate string values from expressions or columns containing a NULL. This function takes a list of strings (or NULLs) and displays all these values in one string. 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:

Solution 2:

Here’s another query option:

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

Here is the result:

name
Linda Jackson
Mary Alice Thomson
Steven
Brown

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:

name
Linda Jackson
Mary Alice Thomson
Steven
Brown

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

Recommended courses:

Recommended articles:

See also:

go to top