SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 difference between '' and "is not null"
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/24/2012 :  04:20:13  Show Profile  Reply with Quote
Today I have come across a strange situation.

I tried a code as below

select * from table1
where field1 is not null


I want all data where field1 is not blank, but the above code didn't produce the required output. It was still displaying all field1 with blank values.

Then I changed the code as below, it worked fine.


select * from table1
where field1 <> ''


I wonder if there are any difference between them. But I usually used to write "is not null"

Edited by - learning_grsql on 09/24/2012 04:21:16

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 09/24/2012 :  04:48:44  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
''(empty string) sting is different from NULL value. NULL Can be treated as a special value, which will be occupied on all datatypes.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/24/2012 :  04:55:10  Show Profile  Visit webfred's Homepage  Reply with Quote
NULL and '' (empty string) are not the same.

You can handle that using ISNULL() or NULLIF()

a) where ISNULL(field1,'') <> ''
b) where NULLIF(field1,'') IS NOT NULL


Too old to Rock'n'Roll too young to die.
Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/24/2012 :  06:41:51  Show Profile  Reply with Quote
Thanks senthil and webfred.

is there any difference between the following?

where field1 <> ''



where isnull(field1,'') <> ''
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 09/24/2012 :  06:50:03  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

Thanks senthil and webfred.

is there any difference between the following?

where field1 <> ''



where isnull(field1,'') <> ''





where field1 <> '' ------> Checks for only empty strings
where isnull(field1,'') <> '' -----> Checks for both NULL and empty stings

--
Chandu
Go to Top of Page

DaleTurley
Yak Posting Veteran

United Kingdom
76 Posts

Posted - 09/24/2012 :  06:53:18  Show Profile  Reply with Quote
I know WHERE ISNULL(field, '') <> '' would work but this query will not be searchable.
The ISNULL function in a WHERE clause is not allowed where I work becuase this...

I'd personally use

WHERE Field <> '' AND Field IS NOT NULL

Edited by - DaleTurley on 09/24/2012 06:56:18
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/24/2012 :  07:07:28  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by DaleTurley

I know WHERE ISNULL(field, '') <> '' would work but this query will not be searchable.
The ISNULL function in a WHERE clause is not allowed where I work becuase this...

I'd personally use

WHERE Field <> '' AND Field IS NOT NULL


Can you please explain the meaning of "searchable query"?
I'm not sure what you mean...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

DaleTurley
Yak Posting Veteran

United Kingdom
76 Posts

Posted - 09/25/2012 :  10:37:07  Show Profile  Reply with Quote
Sorry, what i mean is wherever we use ISNULL in where clauses they result in index / table scans and not seeks.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 09/25/2012 :  15:13:29  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by DaleTurley

Sorry, what i mean is wherever we use ISNULL in where clauses they result in index / table scans and not seeks.



That's true.
Wrapping functions around indexed columns is never a good thing..

Thx for clarification


Too old to Rock'n'Roll too young to die.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000