A few days ago I was asked to look over some SQL statements written by someone new to SQL. They were getting some unexpected results when concatenating strings and although they had managed to overcome the issue, they asked that innocent question we all love to ask and hate to be asked; ‘Why?’
‘Why did it do that and why did I have to do this?’
If, like me, you have been writing SQL for years, string concatenation comes as easy as breathing and being asked why you breath the way you do can cause one to think.
Time to sit down and go through some explanations and examples as to the Why’s and How’s of basic string concatenation.
For the examples, I used an everyday occurrence that is very common within databases; the address. An address is formed from several lines and is often stored in a table as multiple columns (e.g. Line1, Line2, Line3, Town, County, etc…) and every now and then, you will be asked to provide the full address as a single output.
So to start off, we will get some base variables added for the examples.
DECLARE @Line1 VARCHAR(50); DECLARE @Line2 VARCHAR(50); DECLARE @Line3 VARCHAR(50); DECLARE @Town VARCHAR(50); DECLARE @County VARCHAR(50); DECLARE @Postcode VARCHAR(10); DECLARE @Delimiter CHAR(2);
Above we have 6 variables for each part of the address and also a final variable for a delimiter. I prefer using a variable to store the delimiter rather than putting it multiple times within the statement. This not only allows for the delimiter to be easily changed, but also makes it neater when one is using a new line as the delimiter.
(SET @Delimiter = CHAR(13) + CHAR(10);)
Next we need to assign values to these variables.
SET @Line1 = '1 The Street'; SET @Line2 = NULL; SET @Line3 = NULL; SET @Town = 'My Town'; SET @County = 'Countyshire'; SET @Postcode = 'XX1 1ZZ'; SET @Delimiter = ', ';
As you can see from the values above, this particular address does not contain information for Line 2 and Line 3 and so are set to NULL.
So, to our first example. For these examples we will be concatenating strings using the ‘+’ method. It is important to understand this first example. If you don’t, it could catch you out (as it did in this case).
This example concatenates each column and adds the delimiter between each.
SELECT @Line1 + @Delimiter + @Line2 + @Delimiter + @Line3 + @Delimiter + @Town + @Delimiter + @County + @Delimiter + @Postcode AS [FullAddress];
What's wrong with that? Well, here is the golden nugget with string concatenation using ‘+’. The above statement will return NULL. Why I hear you ask. When using the ‘+’ method to concatenate strings, if any one part is NULL, the result will be NULL. Because Line2 is NULL the statement returns a NULL rather than replacing the NULL with an empty string.
Easy enough I hear you say, lets just use the ISNULL function. To an extent you would be correct but depending on how you use it will affect the result. The first instinct is to wrap the variables in an ISNULL like this:
SELECT ISNULL(@Line1, '') + @Delimiter + ISNULL(@Line2, '') + @Delimiter + ISNULL(@Line3, '') + @Delimiter + ISNULL(@Town, '') + @Delimiter + ISNULL(@County, '') + @Delimiter + ISNULL(@Postcode, '') AS [FullAddress];
This is fine, but you then end up with a cluster of delimiters in the middle of the address which is probably not what is desired.
1 The Street, , , My Town, Countyshire, XX1 1ZZ
I know what you are thinking, REPLACE. NO!!! Not the answer. Don’t do it.
Think about what the first example showed us. If any one part is NULL, then NULL is returned. So if we are clever about this, we can use this to our advantage. Look at this statement:
SELECT ISNULL(@Line1 + @Delimiter, '') + ISNULL(@Line2 + @Delimiter, '') + ISNULL(@Line3 + @Delimiter, '') + ISNULL(@Town + @Delimiter, '') + ISNULL(@County + @Delimiter, '') + ISNULL(@Postcode, '') AS [FullAddress];
This statement is pretty similar to the previous one but with one exception. In this statement we have used a concatenated string as the first parameter of the ISNULL function that consists of the address part and the delimiter. Remember, if the address part is NULL, the concatenation will return NULL and therefore will cause the ISNULL function to return the second parameter. This way, you will avoid those additional delimiters.
1 The Street, My Town, Countyshire, XX1 1ZZ
‘Ahhhhh…..’ I hear you say, ‘I’ll remember that.’
It is worth noting that from SQL Server 2012 onwards, a new function is available: CONCAT. The CONCAT function works a little like the ‘+’ method but you pass through each part as a parameter. The most fundamental difference between the two methods is that CONCAT will implicitly cast any parts that are NULL to an empty string. In the case of this post, you would build the statement like this:
SELECT CONCAT(@Line1 + @Delimiter, @Line2 + @Delimiter, @Line3 + @Delimiter, @Town + @Delimiter, @County + @Delimiter, @Postcode) AS [FullAddress];
You would still use the ‘+’ method to concatenate each address part with the delimiter but use the CONCAT function to replace those that return NULL with an empty string.
And that’s it. String concatenation plain and simple. I hope this tutorial is helpful and as usual, constructive criticism and suggestions are welcome.
We'd love to welcome you into our office! We're only 20 miles north of Peterborough, conveniently just off the A16.
Apex Court, Elsoms Way