comparing empty string and null values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

comparing empty string and null values

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2010-02-25 : 12:02:18

declare @curShipDate datetime, @newApptDate datetime
set @curShipDate = null

If @curShipDate<>''
Begin
Set @newApptDate = GETDATE()

End

print @newApptDate

If I run the above sql on my db...it doesnt print any date. Null is not equal to empty string...right? does it have to do with my database setting?

Sachin.Nand

2937 Posts

Posted - 2010-02-25 : 12:05:21

declare @curShipDate datetime, @newApptDate datetime
set @curShipDate = null

If @curShipDate Is Not Null
Begin
Set @newApptDate = GETDATE()

End

print @newApptDate

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 12:08:26

NULL is not stored as a value under default conditions so operators like =,> etc wont work with NULL. you need to use IS NULL or IS NOT NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jaime
Starting Member

19 Posts

Posted - 2010-02-25 : 15:29:23

quote:
Originally posted by cognos79

...Null is not equal to empty string...right?...

NULL is not only not "equal" to empty string, it is also not "not equal" to empty string. Technically, any comparison with NULL results in neither true nor false, it results in NULL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:52:59

quote:
Originally posted by Jaime

quote:
Originally posted by cognos79

...Null is not equal to empty string...right?...

NULL is not only not "equal" to empty string, it is also not "not equal" to empty string. Technically, any comparison with NULL results in neither true nor false, it results in NULL.

Which is what I've suggested above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jaime
Starting Member

19 Posts

Posted - 2010-02-26 : 14:27:45

quote:
Originally posted by visakh16

quote:
Originally posted by Jaime

quote:
Originally posted by cognos79

...Null is not equal to empty string...right?...

NULL is not only not "equal" to empty string, it is also not "not equal" to empty string. Technically, any comparison with NULL results in neither true nor false, it results in NULL.

Which is what I've suggested above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

No, you described how NULLs are stored and said that operators like = and > "won't work". I added my comment for clarity since "won't work" is extremely vague and how NULLs are stored is irrelevant to how they are processed.

Your response actually implied that the reason that comparison that involve a NULL value yeild NULL is because of how NULLs are stored. You can easily see that this is false by playing around with the CONCATENATE NULL YEILDS NULL set option. The option effects how NULLs are processed, but it does not effect how they are stored. Therefore, the storage mechanism of NULLs does not directly influence the processing of NULL values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources