Concat question

Question

Concat question

leehbi

SSCrazy

Points: 2874
More actions
February 11, 2016 at 9:55 am

#308451

I'm trying to understand the concat function.
If I do this :
SELECT custid, country, region, city,
concat(country, N',',region,N',',city) as location
FROM Sales.Customers
Cases where the region is null I get 2 commas : UK,,Liverpool
If I do this :
SELECT custid, country, region, city,
concat(country, N','+region,N','+city) as location
FROM Sales.Customers
I get : UK, Liverpool
What is the difference with the + vs the , in concat? I see nothing in books online about the + syntax.
EDIT :
I think I get it : N','+region is one element hence when null is returned the whole expression returns null as something+null = null hence gets removed by concat. Tell me if I'm wrong.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

It's because CONCAT converts NULLs to empty strings prior to concatenation; that's its difference/advantage to ordinary concatenation with +.

In the first case, when region is null, it's converted to an empty string, so you get exactly that: country followed by a comma, followed by an empty string, followed by a comma, followed by city.

In the second case, you use ordinary concatenation to concatenate the first comma with the region. When the region is null, that concatenation results in a NULL, which is in turn treated as an empty string by CONCAT, eliminating that first comma.

Cheers!

Phil Parkin SSC Guru Points: 246695 More actions · Answer 2

Jacob Wilkins (2/11/2016)
It's because CONCAT converts NULLs to empty strings prior to concatenation; that's its difference/advantage to ordinary concatenation with +.
In the first case, when region is null, it's converted to an empty string, so you get exactly that: country followed by a comma, followed by an empty string, followed by a comma, followed by city.
In the second case, you use ordinary concatenation to concatenate the first comma with the region. When the region is null, that concatenation results in a NULL, which is in turn treated as an empty string by CONCAT, eliminating that first comma.
Cheers!

You can see this exemplified by running the following:

select ',' + NULL

select concat(',', null)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.