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.

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  February 11, 2016 at 10:00 am

  #1858085

  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: 246962

  More actions

  February 11, 2016 at 10:04 am

  #1858088

  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)

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin