Concatenation of Strings!

Now, I know for most of you, this seems like a rather simple idea. The definition of concatenation is, according to is :

verb (used with object)
1. to link together; unite in a series or chain.

Simply put, you combine two or more strings together to form a new string.
So, the first way I learned to concatenate two string together was as shown below:

SELECT ‘book’ + ‘case’;

The output, as I’m sure you have figured out for yourself, is:


Now, one of the big problems with concatenating strings this way is if any value results in NULL. For example,

SELECT ‘book’ + ‘case’ + NULL;

This will give a result of


Now, obviously, this could be very bad if you were actually expecting some data to show up.

Also, you have to watch for things that are not strings showing up in your query. Things like:

SELECT ‘book’ + 1;

Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value ‘book’ to data type int.

Yes, that’s right. Probably not quite what anyone expected. A big, fat error.

Now, starting in SQL Server 2012, things got a little bit easier. We finally got the lovely CONCAT function. All of the issues that we’ve been talking about with the + operator are completely gone away. We can finally do the following statements and not worry about it returning NULL:

SELECT CONCAT('book', 'case', NULL);

Results in


Even better is that the below statement doesn’t result in an error:

SELECT CONCAT(1, 'st down')

It results in:

1st down

Now that is pure greatness. Two functions that work equally well. Two functions that can lead to pure happiness when you’re concatenating strings.

So, next time, we’ll go over some more great functions that can help you with SQL. Until then…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s