September 13, 2007 at 9:55 am
I'm doing a query based on zip codes. The zip codes may or not have the extra 4 digits at the end and I do not know all of the various possibilities. So I have to use the wildcard '%' at the end which prevents me from using = or IN. Is this the only way:
... AND (ZIP LIKE '98101%'
OR Zip LIKE '98104%'
OR Zip LIKE '98154%')....
September 13, 2007 at 10:11 am
... AND Left(Zip, 5) IN ('98101','98104','98154')
🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 13, 2007 at 10:21 am
or you could try something like:
SELECT *
FROM YourTable T
JOIN (
SELECT '98101' UNION ALL
SELECT '98104' UNION ALL
SELECT '98154'
) D (ZIP)
ON T.ZIP LIKE D.ZIP + '%'
September 13, 2007 at 10:26 am
Thanks Jason. That's so smart!
September 13, 2007 at 10:32 am
Not really, it's just that sometimes you are too close to a problem to see it clearly.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgSeptember 13, 2007 at 2:40 pm
Ken's solution is preferable.
It allows to use index, if there is one.
And I would suggest to normalize you data. It eliminates so many problems...
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply