Help with Query Needed

[Scott Tiger]

Ok, I'm stuck. I've read what I have in front of me and Googled around but can't seem to get this.

I'm trying to search a database that has both <NULL> values AND blank values for the same field in different records. I need to filter out both. Here's what I have so far:

SELECT DISTINCT DestCity 
FROM      OppPrimary2  
WHERE   (DestCity IS NOT NULL)  
ORDER BY DestCity

The problem is that the records with "blank" fields - as in empty but not <NULL> - get through this query.

The DestCity field is a VARCHAR with a 19 character space limit. The SQL server is MS SQL Server 2000 if that matters.

Any suggestions?

[cyclist110]

Try something like:

SELECT DISTINCT DestCity
FROM OppPrimary2
WHERE (DestCity IS NOT NULL) OR (DestCity IS NOT "")
ORDER BY DestCity


IS NOT may actually be <>

[Creosote]

You're almost right. Should be:

SELECT DISTINCT DestCity 
FROM OppPrimary2 
WHERE (DestCity IS NOT NULL) AND (DestCity <> '')
ORDER BY DestCity

Note the single quotes, the not equal operator, and the AND.

[cyclist110]

I think the AND will be a problem, it'll look for DestCity being not equal to NULL AND not equal to ' ', whereas using the OR says give me anything that is not equal NULL OR ' '. As for the rest I fully agree.

[Scott Tiger]

You're almost right. Should be:

SELECT DISTINCT DestCity 
FROM OppPrimary2 
WHERE (DestCity IS NOT NULL) AND (DestCity <> '')
ORDER BY DestCity

Note the single quotes, the not equal operator, and the AND.

This works! Thanks for the help! I really appreciate the advice.