Difference Between '' and null

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

General SQL Server Forums

New to SQL Server Programming

Difference Between '' and null

Author

Topic

abuhassan

105 Posts

Posted - 2006-10-17 : 05:44:29

Hi

It may sound like a stupid question...

Is there a difference between '' and null?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-17 : 05:50:54

'' is an empty string
null is undefined

declare @t varchar(10)

select case when @t is null then 'null' else @t end
set @t=''
select case when @t is null then 'null' else @t end

--------------------
keeping it simple...

abuhassan

105 Posts

Posted - 2006-10-17 : 05:54:10

thanks

the reason why i was asking depending on wether i gave a null value or '' to a query i was getting different results

thaks again for clarifying...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-17 : 10:19:58

Note that you can use the isnull and the coalesce functions to find nulls and non-nulls easily, take a look at both of them in in Books Online.

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 10:21:34

Null is the absence of anything. It is not equal to anything, including itself, well because, it doesn't exist.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-17 : 10:23:09

'' is empty and NULL is nothing. Also when you use Null in where clause you should use

Where col is Null

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-17 : 12:10:22

Strictly speaking, null is not "nothing", but rather "undefined".

A zero-length string is defined.

...unless you are using Oracle, which (incorrectly) treats zero-length strings as Nulls.
Yet another way Oracle blows as a development platform.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-18 : 12:38:43

Hello,

From what I have learned NULL means a value that is not known. It could contain something or it could not. It is just not known.

Please correct me if I am wrong.

Kurt

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-18 : 12:57:54

That's correct - which is why the result of any expression that uses a NULL is NULL.

Example
SELECT 2 + NULL
result NULL

because how can the result be known if one of the values in the expression is unknown.

However, sometimes you want to treat all unknowns as an assigned value, you can choose to treat a NULL value as 0 or as a blank string if you want to - but you have to make a choice to do so.

SELECT 2 + ISNULL(NULL, 0)
result 2

or

SELECT 2 + ISNULL(NULL, 2)
result 24

or

SELECT 'Hello' + NULL
result NULL

but

SELECT 'Hello' + ISNULL(NULL, ' world')
result 'Hello world'

or

SELECT 'Hello' + ISNULL(NULL, '')
result 'Hello'

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-24 : 13:17:01

minor note on what looks like a closed topic:

SELECT 2 + ISNULL(NULL, 2)

has a result of 4, not 2...

have a good day :)

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-24 : 13:29:00

Are you saying that it should have a result of 2?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-24 : 13:48:09

No he's just pointing out a typo in my previous post, I've corrected it.

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-26 : 14:52:52

Sorry, was just picking nits....

Sometimes little things set me off and I've got to respond (in hopefully a gentle and maybe humorous way).
like, for e.g. :
when people use the word "for" before the abbreviation e.g. (which means "for example"). So, do they really mean "for for example"? I don't know... I have been biting my toungue so far, since English doesn't seem to be the dominant language for most questioners... (and using Latin in a English statement by a non-english speaker, well that's just asking for a translation problem...)

In this particular case, I guess that many people probably run the code samples (well, I do if I'm not sure what an expected result might be) so I just wanted to point out the small error to clarify the discrepancy to any that might also be running the code.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-27 : 08:02:38

to nit pick your nit picking:
e.g. is short for exempli gratia
Etymology: Latin exempli gratia, for the sake of example : exempli, genitive of exemplum, example + gratia, ablative of gratia, favor.
So litteraly "favor example" or "example favor". Word order does not matter in Latin.

Yes, its before coffee, yes i am pumping my post count. How else can i compete with Kristen's bot?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-10-27 : 11:40:55

hmmm... not positive what your point was. Translating an idea from one language/culture is not done by merely taking a literal word by word swap from one language to the other (which isn't even possible in some cases and merely creates gibberish in many cases). Like you pointed out, e.g. is short for "exempli gratia". The meaning of this phrase is "for the sake of example" or as would be spoken today "for example". To continue the chain of trainslation, as you did, loses the meaning of the original (since "favor example" isn't a meaningful phrase in English).

And on top of all of that, my point would still be valid, using the word "for" in front of the e.g. (or "favor example" or "example favor") would still not make sense.

ugh... off to the coffee pot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources