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.
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Oh I get it. Disregard me first answer.
Select * from zones
Where 'zipcodehere' between left(zone,3) and right(zone, 3)
Select * from zones
Where 'zipcodehere' between left(zone,3) and right(zone, 3)
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.
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),
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
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.
Try my suggestion above then:
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,
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,
Premium Content
You need an Expert Office subscription to comment.Start Free Trial