How to join first,middle and last name in Sql Server | Concatenate strings together in Sql Server

Introduction: In this post I am going to share multiple ways to concatenate/join strings together in sql server.

Description:  Let’s consider an example of joining employee or student’s first name, middle name and last name to create Full name. Suppose we have a table having student’s first, middle and last name in three different columns. First and last name is “not null” field but middle name is nullable field. Now suppose whenever we query this table we want to get student’s full name by combining first, middle and last name. There are many ways to handle this. I have listed some of them here.

Implementation: Let’s create a table and insert some sample data into it to demonstrate our purpose.

CREATE TABLE tbStudent

(

 StudentId           INT NOT NULL PRIMARY KEY IDENTITY(1,1),

 FirstName         VARCHAR(50) NOT NULL,

 MiddleName    VARCHAR(50),

 LastName          VARCHAR(50)NOT NULL,

)

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Ankit','Kumar','Sharma')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Rahul',NULL,'Singh')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Mayank',NULL,'Sharma')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Aman','Singh','Rawat')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Rajesh','Singh','Thakur')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Narender',NULL,'Chauhan')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Puneet','Kumar','Verma')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Varun',NULL,'Shawan')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Jaswinder','Singh','Saini')

INSERT INTO tbStudent (FirstName,MiddleName,LastName) VALUES('Rakesh',NULL,'Sehgal')

Notice that middle name is not entered for some students, so it is null in that cases.

Now let’s concatenate these three fields to create a single field.

First way: Using ‘+’ operator

SELECT FirstName, MiddleName, LastName, Firstname + ' ' + MiddleName+ ' ' + LastName AS FullName FROMtbStudent

Query Result :

FirstName	MiddleName	LastName	FullName
Ankit	Kumar	Sharma	Ankit Kumar Sharma
Rahul	NULL	Singh	NULL
Mayank	NULL	Sharma	NULL
Aman	Singh	Rawat	Aman Singh Rawat
Rajesh	Singh	Thakur	Rajesh Singh Thakur
Narender	NULL	Chauhan	NULL
Puneet	Kumar	Verma	Puneet Kumar Verma
Varun	NULL	Shawan	NULL
Jaswinder	Singh	Saini	Jaswinder Singh Saini
Rakesh	NULL	Sehgal	NULL

As you can see in above result, the FullName is NULL for those rows that have NULL for MiddleName .

Second way: Using ISNULL to handle Null values

The NULL value problem in first way can be resolved by wrapping ISNULL(column,'')around the MiddleName column so that it replaces null values with the empty string.

SELECT FirstName,MiddleName, LastName,Firstname + ' ' + ISNULL(MiddleName,'') + ' '  + LastName AS FullName FROMtbStudent

But if the middle name is null then there will be two spaces instead of one space in between first and last name as shown below.

Query Result :

FirstName	MiddleName	LastName	FullName
Ankit	Kumar	Sharma	Ankit Kumar Sharma
Rahul	NULL	Singh	Rahul  Singh
Mayank	NULL	Sharma	Mayank  Sharma
Aman	Singh	Rawat	Aman Singh Rawat
Rajesh	Singh	Thakur	Rajesh Singh Thakur
Narender	NULL	Chauhan	Narender  Chauhan
Puneet	Kumar	Verma	Puneet Kumar Verma
Varun	NULL	Shawan	Varun  Shawan
Jaswinder	Singh	Saini	Jaswinder Singh Saini
Rakesh	NULL	Sehgal	Rakesh  Sehgal

Third way: Using COALESCE to handle Null values

The null value problem in first way can also be resolved by using COALESCE. The COALESCE function in SQL returns the first non-NULL expression among its arguments. So it will return the value of MiddleName field if not null and will return empty string if it finds null value in MiddleName field.

SELECT FirstName, MiddleName, LastName, Firstname + ' ' + COALESCE(MiddleName,'') + ' ' + LastName AS FullName FROM tbStudent

But still if the middle name is null then there will be two spaces instead of one space in between first and last name.

Fourth way: Using COALESCE to handle Null values (Correct Use)

Below is the correct way of combining first, middle and last name without extra space in between first and middle name.

SELECT FirstName, MiddleName, LastName, FirstName + ' ' + COALESCE(MiddleName+ ' ','') + Lastname ASFullName FROM tbStudent

Query Result :

FirstName	MiddleName	LastName	FullName
Ankit	Kumar	Sharma	Ankit Kumar Sharma
Rahul	NULL	Singh	Rahul Singh
Mayank	NULL	Sharma	Mayank Sharma
Aman	Singh	Rawat	Aman Singh Rawat
Rajesh	Singh	Thakur	Rajesh Singh Thakur
Narender	NULL	Chauhan	Narender Chauhan
Puneet	Kumar	Verma	Puneet Kumar Verma
Varun	NULL	Shawan	Varun Shawan
Jaswinder	Singh	Saini	Jaswinder Singh Saini
Rakesh	NULL	Sehgal	Rakesh Sehgal

Fifth way: Using CONCAT inbuilt function(sql server 2012 and further versions)

In SQL Server 2012 there is a new function called CONCAT that accepts multiple string values including NULLs as arguments. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. Since CONCAT substitutes NULLs with empty string, thus it eliminates the need of additional task for handling NULLs. For example

SELECT FirstName, MiddleName, LastName, CONCAT(FirstName , ' ', MiddleName , ' ' ,Lastname) AS FullName FROM tbStudent

But still if the middle name is null then there will be two spaces instead of one space in between first and last name.

Now let’s consider one more case where all the columns e.g. First, Middle and Last Name columns are declared as Nullable. That means any of the columns values can be null. Then this can be correctly handled as:

SELECT FirstName,MiddleName,LastName, REPLACE(RTRIM(COALESCE(FirstName + ' ','') + COALESCE(MiddleName + ' ','')+ COALESCE(LastName + ' ','')), SPACE(2),SPACE(1)) AS FullName FROMtbStudent 


**************************** End *************************