Now, I know for most of you, this seems like a rather simple idea. The definition of concatenation is, according to Dictionary.com 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:
——————
bookcase
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
——————
NULL
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
——————
bookcase
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…