Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Published: May 06, 2016
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.