... where zip not like (subquery) ?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

... where zip not like (subquery) ??

Author

Topic

blake
Starting Member

29 Posts

Posted - 2006-07-17 : 20:19:19

Here's what I'm trying to do. I have a table with zip codes in them where the zipcodes might be only the first part of the zipcode. eg: instead of listing 90201, 90202, 90203, ..., 90299 the table might just contain 902 to represent all of them.

If it contains only full zipcodes, I can issue a query such as:

select name, city from contacts
where contacts.zip not in (select zip from zipcodes)

What I want to do is something like the following invalid SQL, but I'm not sure how to properly structure the SQL or even if it's possible to do in a single SQL call without using cursors:

select name, city from contacts
where contacts.zip not like (select zip + '%' from zipcodes)

Any pointers on how best to do this?

Thank you!

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-07-17 : 20:50:05

You might be able to do this.


SELECT name, city from contacts
WHERE NOT EXISTS(SELECT * FROM ZipCodes WHERE contacts.zip LIKE ZipCodes.Zip + '%')

blake
Starting Member

29 Posts

Posted - 2006-07-18 : 15:18:43

Thanks... that appears to work great! I haven't used EXISTS before so I'll definitely be reading up on it as it looks quite useful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources