Using CONCAT
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.

Your SQL Trainer
Kevin Urquhart

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

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron