| Author |
 Topic  |
|
|
learning_grsql
Posting Yak Master
155 Posts |
 Posted - 09/24/2012 : 04:20:13
|
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
Aged Yak Warrior
India
997 Posts |
Posted - 09/24/2012 : 04:48:44
|
''(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 |
 |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 09/24/2012 : 04:55:10
|
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. |
|
|
|
learning_grsql
Posting Yak Master
155 Posts |
Posted - 09/24/2012 : 06:41:51
|
Thanks senthil and webfred.
is there any difference between the following?
where field1 <> ''
where isnull(field1,'') <> '' |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 09/24/2012 : 06:50:03
|
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 |
|
|
|
DaleTurley
Yak Posting Veteran
United Kingdom
76 Posts |
Posted - 09/24/2012 : 06:53:18
|
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 |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 09/24/2012 : 07:07:28
|
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. |
|
|
|
DaleTurley
Yak Posting Veteran
United Kingdom
76 Posts |
Posted - 09/25/2012 : 10:37:07
|
| Sorry, what i mean is wherever we use ISNULL in where clauses they result in index / table scans and not seeks. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 09/25/2012 : 15:13:29
|
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. |
|
|
| |
Topic |
|
difference between '' and "is not null"
