SQL and ML Spaces

SQL Server 2012 introduced a new function called as concat().

Previous versions of SQL Server, the null values in any of the concatenated fields was handled by either using ISNULL or COALESCE functions or by CASE END statements.

Concat handles NULLs in any of the participating fields and suppresses them without additional code handling.

So use CONCAT() wherever applicable.

[cc lang=”SQL”]

— sample case when middle name is null
select * from [dbo].[employee]
where emp_id = ‘PMA42628M’

— simple concatenation fails
select fname + minit + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

— handling NULLs before SQL Server 2012
——————————————————————————-
select fname + isnull(minit, ‘ ‘) + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

select fname + coalesce(minit, ‘ ‘) + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’

select fname + case when minit is null then ‘ ‘ else minit end + lname as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’
——————————————————————————-

— handling NULLs after SQL Server 2012
——————————————————————————-
select concat(fname, ‘ ‘,minit, ‘ ‘, lname) as FullName from [dbo].[employee]
where emp_id = ‘PMA42628M’
——————————————————————————-

[/cc]