How to concatenate columns in SQL Server? Example Tutorial

Hello guys, welcome to my new blog SQLrevisited. I have long back created this blog to exclusively share SQL and Database articles but never get time to start it until today. This is the first article on this blog and in this post, you will learn how to contact multiple columns in an SQL server.  Like Java and many other programming languages and databases, you can use the + operator to concatenate multiple columns in SQL Server. You can join as many columns as you want with the + operator but you must remember that concatenating with NULL will produce NULL, which means you can lose the information from other columns if one of them is NULL. 

If this is not acceptable and I guess it would be, then use either ISNULL() or COALESCE() to handle NULL values in Microsoft SQL Server. 

You can use these functions to replace NULL with empty String or blank, or any default value of your choice like 'NA' or 'Nil'. You can also use CONCAT() function to join multiple columns. 

Since CONCAT() implicitly converts NULL values to empty String you don't need to use ISNULL() or COALESCE(), but remember, CONCAT() is not available in all SQL Server versions like SQL SERVER 2008, and the lower version doesn't have it. 

3 Ways to concatenate multiple columns in SQL 

In this article, I will show you examples of both + operator and CONCAT() functions to concatenate multiple columns in SQL Server.

1. Concatenating Multiple Columns using + Operator in SQL

In order to understand the example better let's create a sample table with some NULL values. Here we have the #People table with contains first and last names. Both of them can be null and we need to construct a full name by concatenating these two columns, here is our first solution.

IF OBJECT_ID( 'tempdb..#People' ) IS NOT NULL

DROP TABLE #People;

CREATE TABLE #People (first_name varchar(30), last_name varchar(30));
INSERT INTO #People VALUES ('Keith','Duke');
INSERT INTO #People VALUES ('Mandana', NULL);
INSERT INTO #People VALUES (NULL, 'Lauren');

-- cleanup
DROP TABLE #People

SQL query to print first, last, and full name from People table, full_name is the concatenation of column first_name and last_name from People table.

SELECT first_name, last_name, first_name + last_name as full_name from #People

first_name    last_name             full_name
Keith         Duke                  KeithDuke
Mandana       NULL                  NULL
NULL          Lauren                NULL

You can see that full_name is NULL for the second and third-row because for them either of one column is NULL. In order to avoid that, we need to use ISNULL() or COALESCE() function to replace NULL values with empty String in SQL SERVER. 

2. String concatenation with ISNULL()

Now, let's see an example of how you can use the ISNULL() function to deal with NULL values and create meaningful output while concatenating multiple columns in SQL Server. 

SELECT first_name, last_name, ISNULL(first_name,'') + ISNULL(last_name,'') as full_name 
from #People

first_name last_name full_name

Keith Duke KeithDuke

Mandana NULL Mandana

NULL Lauren Lauren

You can see that now full_name is not null even though one of the joining columns is NULL. Similarly, you can use COALESCE() function as shown in the following SQL query:

SELECT first_name, last_name, 
COALESCE(first_name,'') + COALESCE(last_name,'') as full_name 
from #People

first_name last_name full_name

Keith Duke KeithDuke

Mandana NULL Mandana

NULL Lauren Lauren

You can see the identical result similar to ISNULL(), by the way when you use COALESCE() function, you can also use the value of another column if the target column is null, as shown here.

3. Concatenating multiple columns using CONCAT function

There is another way to concatenate or join String or different columns in SQL Server, by using the contact() function, here is an example of that. 

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

Result

-------------------------

Happy Birthday 11/25



(1 row(s) affected)

That's all about how to contact multiple columns in SQL SERVER. You can use the + operator or CONCAT() function to concatenate columns but remember + operator returns NULL if one of the columns is NULL, which means you need to use ISNULL() or COALESCE() to handle NULL values. CONCAT() implicitly convert NULL values to empty String, but not available on SQL SERVER 2008 and below.