T-SQL: Concatenate something + null=null

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: