null column concatenation

[tran008]

If col1 is null...
this statement doesn;t seem to work.
update table set col1=col1 +'-VAR' where id=idNumber. Alway return Null value.

this will work
update table set col1=isnull(col1,'')+'-VAR' where id=idNumber

Can anyone explain this? I'm using sql 2000.

 

[ESquared]

col1 must have null values in it. Nulls propagate. They represent the value "don't know the value." So what do you have when you add "dont' know the value" to a value? You get "don't know the value" again.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[SQLDenis]

just to add to Esquared's comment

This is because by default the CONCAT_NULL_YIELDS_NULL setting is set to ON

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Ah, nulls.

NULL is not equal to anything, even other NULLs. Any comparision with NULL always results in an unknown value.

BTW - COALESCE and CASE are preferred over ISNULL.

< M!ke >
I am not a hamster and life is not a wheel.

 

[SQLDenis]

>>Any comparision with NULL always results in an unknown value

depending on the ANSI_NULLS setting of course (which by default is on)

if null = null
print 'yes'
else 
print 'no'



set ansi_nulls off
go

if null = null
print 'yes'
else 
print 'no'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[nice95gle]

Look at this thread from a while back. It's some good reading.

http://www.tek-tips.com/viewthread.cfm?qid=1348684&page=1

Well Done is better than well said
- Ben Franklin

 

[tran008]

thanks everyone...Good edumaction about SQL NULL.