Altius Altius - 11 months ago 89
SQL Question

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

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

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download