concatenate first and last name in sql server

Concatenate many rows into a single text string using SQL

Hi , Some times we need to show full names of user in report, i.e have concatenate first-name, surname, last-name in a column. there are multiple way to concatenate names to full-name. 

Here i have drawn one of best .

please find my query to concatenate first and last name in sql server

       --Create Temp Table

CREATE table #Emp(first_name varchar(50), middle_initial varchar(50), last_name varchar(50))
  
--Insert records to Temp Table
insert into #Emp
select 'Harshad', null, 'Krishna' union all
select 'Harshad', 'J.', 'Krishna' union all
select 'Harshad', 'J.', null union all
select 'Harshad', null, null union all
select null, null, null

select
    *,
            isnull(' ' + nullif(first_name, ''), '') +
            isnull(' ' + nullif(middle_initial, ''), '') +
            isnull(' ' + nullif(last_name, ''), '') AS FN,
    stuff
    (
        isnull(' ' + nullif(first_name, ''), '') +
        isnull(' ' + nullif(middle_initial, ''), '') +
        isnull(' ' + nullif(last_name, ''), ''),
        1, 1, ''
    )AS FullName
from #Emp

-- Clear Temp Table

DROP TABLE #Emp

NULLIF
NULLIF returns null if it comparison is successful.



SELECT '' + NULLIF('Harshad','Harshad')

it returns NULL.