MySQL Query - how to select rows based on range value

Question

Having a bit of an issue with this one. Is there a way to check if a value is in the range provided in a mysql query result.

How can I see if the Zip Code 85012 is within the ZipCodes field returned without first making a query, breaking it up into parts, and using BETWEEN?

+---------+-------------+--------------------+------------+-------------+
| StyleID | HistStyleID | AutobuilderStyleID | FilterRule | ZipCodes    |
+---------+-------------+--------------------+------------+-------------+
|  355755 |  2013490103 | w2013k49m1t3       | includes   | 27000-36999 |
+---------+-------------+--------------------+------------+-------------+

:: UPDATE ::

This table can also contain various ranges as follows..

+---------+-------------+--------------------+------------+-------------------------------------+
| StyleID | HistStyleID | AutobuilderStyleID | FilterRule | ZipCodes                            |
+---------+-------------+--------------------+------------+-------------------------------------+
|  332492 |  2012493107 | w2012k49m31t7      | excludes   | 38600-39799,70000-71499,71600-79999 |
+---------+-------------+--------------------+------------+-------------------------------------+

Does you Zipcodes field holds just 2 zipcodes or more than that? Any ways it looks shabby. — Praveen Prasannan
– Praveen Prasannan, Commented Aug 5, 2013 at 17:52
Looking at the table it can be various ranges. 38600-39799,70000-71499,71600-79999 — Carlos
– Carlos, Commented Aug 5, 2013 at 17:54
This sounds like a table normalization problem. I would probably add a table that relates to this table and has ZipCodeRangeStart and ZipCodeRangeEnd fields. You could then JOIN that table to this based on the value you are interested being BETWEEN those values. — Mike Brant
– Mike Brant, Commented Aug 5, 2013 at 17:58
This database is from Chrome Data so redesign is unfortunately not an option. — NickNo
– NickNo, Commented Dec 16, 2014 at 3:43

Maxime Lorant · Accepted Answer · 2013-08-05 18:09:11Z

1

Like it's been suggested in the comments, you should redesign your database:

CREATE TABLE Styles (
     StyleID INT PRIMARY KEY,
     HistStyleID INT, 
     AutobuilderStyleID VARCHAR(40),
     FilterRule VARCHAR(40)
)


CREATE TABLE ZipCodes (
     StyleID INT,
     RangeBegin INT,
     RangeEnd INT,

     FOREIGN KEY StyleID REFERENCES Styles(StyleID)
)

Then you can make queries like:

SELECT DISTINCT StyleID FROM ZipCodes WHERE RangeBegin >= 85012 AND RangeEnd <= 85012

answered Aug 5, 2013 at 18:09

Maxime Lorant

36.4k19 gold badges91 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Query - how to select rows based on range value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related