query where data field is a range

iceman19330
iceman19330 used Ask the Experts™
on
So I have a shipping table with the first three of the zip code, the issue is that certain areas have certain delivery zones.  so the table rather then having each 3 digit code have its own row, its grouped together for certain ranges, and the ranges can be 2-30 between start and finish in the range of zip codes.  for example.  one might be 004-005, while another might be 010-043 and so on and so forth.  is there a way to query for the range, so lets say someone is in a zip code that starts with 032, which would be in the 010-043 range.  is that possible?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Aaron TomoskyDirector of Solutions Consulting

Commented:
Where zip between 11111 and 11121
Aaron TomoskyDirector of Solutions Consulting

Commented:
Oh I get it. Disregard me first answer.

Select * from zones
Where 'zipcodehere' between left(zone,3) and right(zone, 3)

Author

Commented:
but the field might be 010-043, those wouldn't be broken out by themselves.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Disregard my first reply.  :D
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
http:#a35766565 is very close, but since you only have the 1st three of zip code and likely have full 5+ digit zone code in the shipping table you will need to do something a little different.  The first choice that comes to mind is to put LEFT(..., 3) on the zip code from the shipping table, but note this may hurt performance of index on that column.

Therefore, you could try this alteration to http:#a35766565

Select * from zones
Where 'zipcodehere' between concat(left(zone,3), '00') and concat(right(zone, 3), '99')

That should find all the strings in rang of 01000 to 04399 for example and so that should work given the leading zeros make the alphabetic sort same as the numeric one in this instance.

Author

Commented:
Here is an example of the data that I am pulling.  I tried couple to see the results and I get 0 results so still not sure the sql is correct.

 
INSERT INTO `shipping_zone` (`dest_zip_code`, `zone`) VALUES
('006-007', 45),
('004-005', 3),
('008', 0),
('009', 45),
('010-043', 2),
('044', 3),
('045', 2),
('046-047', 3),
('048-062', 2),
('063-066', 3),
('067', 2),
('068-083', 3),
('084', 4),
('085-089', 3),
('090-099', 0),
('100-119', 3),
('120-126', 2),
('127', 3),
('128-129', 2),
('130-132', 3),
('133-135', 2),
('136-139', 3),
('140-143', 4),

Open in new window

Author

Commented:
If I query for this SELECT *  FROM wp_wpsc_shipping_zone WHERE dest_zip_code BETWEEN LEFT(  '02312', 3 )  AND RIGHT(  '02312', 3 ) i get

dest_zip_code      zone
044      3
045      2
046-047      3
048-062      2
063-066      3
067      2
068-083      3
084      4
085-089      3
090-099      0
100-119      3
120-126      2
127      3
128-129      2
130-132      3
133-135      2
136-139      3
140-143      4
144-146      3
147      4
148-149      3
150-168      4
169      3
170-174      4
175-196      3
197-199      4
200-241      4
242-243      5
244-245      4
246-248      5
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
Guess I am confused at what you are trying to do.  My original reading of the question is that you have the shipping zone values '046-102' and you are trying to use them to query a different table of actual zip codes.  Are you trying to query the shipping zones themselves that have the hyphen in between two 3 character zipcode prefixes?

Author

Commented:
The zip codes are the values '048-062' etc and the shipping zone is '2', '3', etc.  So what I am trying to do, and this has changed a bit since the beginning, is to take the shipping zip code from the customer say 05477 and check the table wp_wpsc_shipping_zone to see what zone that is in, which according to the data above that is in zone 2.
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Try my suggestion above then:
Select * from zones
Where 'zipcodehere' between concat(left(zone,3), '00') and concat(right(zone, 3), '99')

The 'zipcodehere' is the variable or column from customer/sales order table and the zone (i.e., left(zone, 3)) is the column with values '048-062' in your shipping zone table.  You can then return the true zone column.  I had original just typed in as an example.  I see it may have caused confusion since you have a column called zone.

Example:
SELECT zone
FROM wp_wpsc_shipping_zone
WHERE 'customer zip code' BETWEEN CONCAT(LEFT(dest_zip_code, 3), '00') AND CONCAT(RIGHT(dest_zip_code, 3), '99');

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial