How to combine first name, middle name and last name in SQL server

Question

you can refer the below queries to get the same-

1

select FirstName +' '+ MiddleName +' ' + Lastname as Name from TableName.

2

select CONCAT(FirstName , ' ' , MiddleName , ' ' , Lastname) as Name from 
  TableName

3

select Isnull(FirstName,' ') +' '+ Isnull(MiddleName,' ')+' '+ Isnull(Lastname,' ') 
from TableName.

Note: Point 1 query return if all columns have some value if anyone is null or empty then it will return null for all, means Name will return "NULL" value.

To avoid the point number 1, you can use point number 2 or point number 3 -

We can use IsNull or CONCAT keyword to get the same.

If anyone containing null value then ' ' (blank space) will add with next value.

Answer 1

• Using a combination of RTRIM and LTRIM will strip any white-space on each end.
• CONCAT to append the name segments together
• COALESCE to replace NULL with an empty string

Formatted for readability

SELECT 
    RTRIM(LTRIM(
        CONCAT(
            COALESCE(FirstName + ' ', '')
            , COALESCE(MiddleName + ' ', '')
            , COALESCE(Lastname, '')
        )
    )) AS Name
FROM TableName

Answer 2

Replace function removes two character white-spaces while concatenating First, Middle and Last Name.

SQL2016 and above compatible code

SELECT REPLACE(CONCAT(FirstName+' ',MiddleName+' ',LastName+' '),'  ',' ') 
AS EmployeeName
FROM dbo.Employee

SQL SERVER 2017 Compatible code:

  SELECT REPLACE(CONCAT_WS(' ',FirstName,MiddleName,LastName),'  ',' ')AS 
  EmployeeName FROM dbo.Employee