Tech Sharing

Tech Sharing

Advertisement

T-SQL: Concatenate something + null=null

By on April 11, 2009

mssql2005This whole morning just spent about 1 hours+ just to try out one very simpel query on MS SQL 2005.

I keeping get the null value whenever i run this query, even in my program or run directly from the MS SQL 2005 management studio.

Finally I found out that is you are concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL.


With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, String + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is String?


This is the case 1

Update tableA
set field1 + ‘My value’
where field2 = 1


This is the case 2

select ‘test ‘ + null + ‘ string’

returns
————
NULL


This is the solution

set concat_null_yields_null off
select ‘test ‘ + null + ‘ string’

returns
————
test string

Comments

No Responses to “T-SQL: Concatenate something + null=null”

Write a Comment

Captcha *

%d bloggers like this: