Where "column" <> null? - SQL Server Forums

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

Where "column" <> null?

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2010-09-22 : 08:16:59

How do I filter results so that only rows with data in them display? Is it null?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-22 : 08:20:15

the syntax to skip a null value would be


WHERE
    [col] IS NOT NULL

NULLS aren't evaluable any other way. To check for a NULL you use xyz IS NULL.

[col] <> NULL returns NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-22 : 09:39:29

len([col]) > 0

meef
Posting Yak Master

113 Posts

Posted - 2010-09-22 : 09:54:05

quote:
Originally posted by Transact Charlie

the syntax to skip a null value would be

WHERE
[col] IS NOT NULL

NULLS aren't evaluable any other way. To check for a NULL you use xyz IS NULL.

[col] <> NULL returns NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Hmm, I tried that and it didn't work. I don't think it should matter that it's currently a SELECT statement in Visual Foxpro that I am using this, should it?

This is my SELECT statement:

SELECT acct, scac, COUNT(aud) FROM btsmaster WHERE aud IS NOT NULL GROUP BY acct, scac

I'll try slimt's suggestion too.

EDIT: That's not working either... These fields just show as empty in the table, they don't actually say NULL or anything.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-09-22 : 10:05:26

empty string is not the same thing as NULL. NULL means unknown. empty string is a known value.

empty string is like a box that is known to contain nothing.

NULL is like a box with unknown contents. could be nothing, could be a diamond, could be a dog turd.

which box would you rather have?

elsasoft.org

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-22 : 10:09:22

if it's a varchar and contains an empty string then try


WHERE
    aud <> ''

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-22 : 10:15:35

quote:
Originally posted by slimt_slimt

len([col]) > 0

Will not make use of index if available

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources