Concatenating Strings to NULL values

Question

Concatenating Strings to NULL values

Bru Medishetty

SSC-Insane

Points: 23606
More actions
January 31, 2010 at 4:36 pm

#220149

Comments posted to this topic are about the item Concatenating Strings to NULL values
Bru Medishetty
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty

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

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Interestingly, I found the database option "concat null yields null" when set to either True or False, it yields null values only.. unless SET CONCAT_NULL_YIELDS_NULL ON is specified..

I could not upload in the comments section, so find them here..

Test script with the option turned ON

Test script with the option turned OFF

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

TomThomson SSC Guru Points: 104773 More actions · Answer 2

I think that what is going on here is that the properties of the connection are over-riding the DB option - the connection supporting a QA or SMSS query window always has concat null yields null ON for the connection until it is overridden by a SET statement. The db option is only relevant for connections where concat null yields null is not set for the connection, so it is not relevant to QA or SMSS query windows.

Tom