query where data field is a range

Where zip between 11111 and 11121

Oh I get it. Disregard me first answer.

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

but the field might be 010-043, those wouldn't be broken out by themselves.

Disregard my first reply.  :D

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.

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),

Select allOpen in new window

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

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?

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.