How to join first,middle and last name in Sql Server | Concatenate 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 FROM tbStudent

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 FROM tbStudent

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 AS FullName 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 FROM tbStudent

Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..