Sql Query to get the Full name from the table Employee Eliminating Null Value

Question

I have an EmpName Table.

Select * from EmpName

NameID FirstName MiddleName LastName

1       Sam         NULL      NULL

2       NULL        Todd      Tarzan

3       NULL        NULL      Sare

4       Ben         Parker    NULL

5       James       Nick      Nancy

Now I write the following query to get the fullname as

Select FirstName + ' ' + MiddleName + ' ' + LastName 

As FullName from EmpName

But I am getting the following result -

FullName

   NULL

   NULL

   NULL

   NULL

James Nick Nancy

But I want the following result -

FullName

   Sam

Todd Tarzan

   Sare

Ben Parker

James Nick Nancy

Is it -

String

concat with

Null

returns the

Null

?

How can I get the

FullName

whose

MiddleName

or

LastName

has the value Null

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

When you concatenate strings, NULL takes precedence. So, use COALESCE(). This is a pain with separators, but the following should do what you want:

Select ltrim(coalesce(' ' + FirstName, '') +
             coalesce(' ' + MiddleName, '') +
             coalesce(' ' + LastName)
            ) as FullName
From EmpName;

Sql Query to get the Full name from the table Employee Eliminating Null Value

Sign up