SQL Server filtering out string but leave empty strings

Question

I have a table with with details of my companies products, and one of the columns has categories for the products but also empty strings for products that have not yet been categorized.

I'm trying to filter out the category 'trash' and leave the rest including the empty ones, but when I apply the filter, the empty ones are disappearing too... is this normal? how can I filter out the 'trash' and leave everything else including empty values?

I'm using a WHERE clause like this:

WHERE 
    table.month = 8 AND table.category <> 'trash'

Show us your data. I Guess the disappeared items have NULL values. — Tedo G.
– Tedo G., Commented Aug 12, 2016 at 9:12
Are you sure that there are items for month = 8 that have category <> 'trash'? — Thorsten Dittmar
– Thorsten Dittmar, Commented Aug 12, 2016 at 9:16
@ThorstenDittmar Nope, not in SQL Server as you can see here. data.stackexchange.com/stackoverflow/query/523606 — Mikael Eriksson
– Mikael Eriksson, Commented Aug 12, 2016 at 9:23
@TedoG. Sorry, I got mixed up here. Had to bring the extended logic tables back to my mind... :-) — Thorsten Dittmar
– Thorsten Dittmar, Commented Aug 12, 2016 at 9:28

Thorsten Dittmar · Accepted Answer · 2016-08-12 09:25:57Z

4

Probably you have entries with category NULL. Thus, they will not be returned, because by definition the comparison null <> 'trash' is neither true nor false, but a WHERE clause will only return records where all conditions are true.

You could modify your statement to this:

WHERE table.month = 8 AND ISNULL(table.category, '') <> 'trash'

or this:

WHERE table.month = 8 AND (table.category is null OR table.category <> 'trash')

This former replaces null values with empty strings, which should return what you want.

answered Aug 12, 2016 at 9:25

Thorsten Dittmar

56.9k10 gold badges95 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ricardomadaleno Over a year ago

thank you so much, the first statement worked perfectly. I had no idea that NULLs would behave like this. Thank you

Collectives™ on Stack Overflow

SQL Server filtering out string but leave empty strings

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related