Avatar of iceman19330
 asked on

query where data field is a range

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?
MySQL Server

Avatar of undefined
Last Comment
Kevin Cross

8/22/2022 - Mon
Aaron Tomosky

Where zip between 11111 and 11121
Aaron Tomosky

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Disregard my first reply.  :D
Kevin Cross

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

Open in new window

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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 Cross

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.
Your help has saved me hundreds of hours of internet surfing.
Kevin Cross

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.