Concat First Middle and Last name in Sql

Hey folks till now we have discussed Performance improvement of cursor SQL Server, User defined functions in sql, Kill Processes in sql server 2008, View in sql , default constraint in sql , Remove Cross duplicate rows in SQL, Recursive SQL Query , Recursive SQL Query-2, STUFF and CONCAT in SQL, RANK in SQL server , Difference between temporary table and table variable in sql server, UNIQUEIDENTIFIER in SQL Server, RAW Mode with FOR XML , AUTO Mode with FOR XML, EXPLICIT Mode with FOR XML , PATH Mode with FOR XML, OUTPUT Clause in SQL Server, Difference between delete and truncate in sql server etc.

Today I am going to explain very important concept, how to Concatenation of First Middle and Last name in Sql  and how could we handle NULL values and discard extra spaces from concatenation.

Lets check with below sample code:

DECLARE @firstname VARCHAR(MAX)='FirstName'
DECLARE @middlename VARCHAR(MAX)=NULL
DECLARE @lastname VARCHAR(MAX)='Last'

--Query solution 1
SELECT '|'+RTRIM(LTRIM(RTRIM(isnull(@firstname,'') 
                     + ' ' + isnull(@middlename,'')) 
                     + ' ' + isnull(@lastname,'')))+'|'
                     
--Query solution 2
SELECT '|'+RTrim(Coalesce(@firstname + ' ','') 
+ Coalesce(@middlename + ' ', '')
+ Coalesce(@lastname + ' ', '')
)+'|'