Using CONCAT
Published: 6th October 2014
This is another new function in SQL Server 2012 which is incredibly useful and I’ve been using everywhere I can in place of the previous ISNULL or COALESCE functions.

Basically this function, as you would guess, concatenates strings into one large string. Nothing too exciting there because we have the ability to do this using +… but where this comes into a league of its own is that CONCAT caters for NULLS… something that we would otherwise struggle to do.

I’ll demonstrate this with a simple example.

Using the AdventureWorks2012 database we’ll simply obtain the full name (including title) of all the people listed in the Person.Person table.

We’ll use the simple “+” syntax and nothing else…

select BusinessEntityID, title, firstName, lastName,
      
title + firstName + lastName as fullName
from person.person


You can clearly see the problem here and this is the most common reason for annoyance… If any column is NULL then SQL Server immediately returns the entire row as NULL. Not handy.

This is why we use ISNULL or COALESCE. So let’s try again…

select BusinessEntityID, title, firstName, lastName,
      
isnull(title + ' ', '') + isnull(firstName + ' ', '')  + isnull(lastName + ' ', '') as fullNameIsNull,
      
coalesce(title + ' ', '') + coalesce(firstName + ' ', '')  + coalesce(lastName + ' ', '') as fullNameCoalesce
from person.person


This works just fine, but it’s very messy and clunky… so how does the CONCAT function help? Well it deals with the NULL values for you, therefore meaning you can have a lot cleaner code…

select BusinessEntityID, title, firstName, lastName,
      
concat(title + ' ', firstName + ' ', lastName + ' ') fullName
from person.person


Bingo… much neater and cleaner yet providing the results we need.

This is definitely a function I’ll be using a lot.
Comments:
NB: Comments will only appear once they have been moderated.