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?

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)

iceman19330

ASKER

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

iceman19330

ASKER

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.

iceman19330

ASKER

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

iceman19330

ASKER

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?

iceman19330

ASKER

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.

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial