Using <> or != within query notworking with a stri

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 2008 Forums

Transact-SQL (2008)

Using <> or != within query notworking with a stri

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-12-17 : 10:56:49

I am trying to use teh below two queries i am getting zero rows as my result.

there should be 1800 count.what is teh right way to use not equal to on a varchar column.

select COUNT(*) from TAB_PM where Building != 'BLHT';

select COUNT(*) from TAB_PM where Building <> 'BLHT';

Thanks a lot for the helpful info.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-17 : 11:02:31

those are equivalent and both are correct for MS Sql Server.

both of these return 2:


select count(*)
from   (select 'tg' as building union all
       select 'cplusplus' union all
       select 'BLHT'
       ) as TAB_PM
--where  Building <> 'BLHT'
where  Building != 'BLHT'

OUTPUT:
2

Be One with the Optimizer
TG

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-12-17 : 11:49:36

What column type is Building? Are there leading/trailing characters?

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-17 : 14:17:00

>>What column type is Building? Are there leading/trailing characters?
If leading spaces were not satisfying his criteria then count(*) should be total rows in the table - not zero.

cplusplus, Could all your values be 'BLHT'? Please post your actual code. re: "zero rows as my result"
are you getting:


-----------
0

(1 row(s) affected)

or are you getting:

-----------------------

(0 row(s) affected)

Be One with the Optimizer
TG

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-12-17 : 14:39:49

Sorry about that. Got myself confused.

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-17 : 14:58:47

quote:
Originally posted by djj55

Sorry about that. Got myself confused.

djj

No worries - happens to me often

Be One with the Optimizer
TG

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-17 : 15:04:04

NULL values will not match any form of "not equal" on a string. You must explicitly test for NULL:



select COUNT(*) from TAB_PM where Building IS NULL OR Building != 'BLHT';

select COUNT(*) from TAB_PM where Building IS NULL OR Building <> 'BLHT';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources