Concatenate SQL Server Columns that contain NULL values

One way to concatenate multiple strings or columns is to use the "+" operator.

We will use name columns from Person.Person table in AdventureWorks database, that has some NULL values in the MiddleName column:

select_person_name

To concatenate columns using the "+" operator, execute the following query:

USE AdventureWorks
GO
SELECT FirstName + ' ' + MiddleName + ' ' + LastName AS Name
FROM Person.Person
GO

concatenate_columns_using_operator

For the rows where MiddleName is NULL, the concatenation result will be NULL.

You can use ISNULL function to check if MiddleName value is NULL, and to replace it with an empty for concatenation:

USE AdventureWorks
GO
SELECT FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS Name
FROM Person.Person
GO

concatenate_columns_using_operator_and_isnull

In SQL Server 2012 and later you can use CONCAT function to get the same result:

USE AdventureWorks
GO
SELECT CONCAT (FirstName, ' ', MiddleName, ' ', LastName) AS Name
FROM Person.Person

concatenate_columns_using_concat_function

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">