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
Select FirstName + ' ' + MiddleName + ' ' + LastName
As FullName from EmpName
FullName
NULL
NULL
NULL
NULL
James Nick Nancy
FullName
Sam
Todd Tarzan
Sare
Ben Parker
James Nick Nancy
String
Null
Null
FullName
MiddleName
LastName
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;