MYSQL query - checking if integer between range in single column string

Question

I have a function which accepts an integer then checks it against a MYSQL table to check if the number falls between the ranges of a a records defined in the table.

The only thing is, my date ranges are defined in a single column as a string in the following format:

0-25

When making a query against this, is there any way I can parse the value in the cell to create a 'minvalue' and 'maxvalue' range?

So far I have:

"SELECT * FROM high_performing_instructors_reference WHERE score BETWEEN....."

I can edit the tables to add min and max values and use PHP explode but I wanted to see if there was a way of doing this in MYSQL first. Thanks

Normalize. Your. Data. That's it. No other way to do this properly. — Alma Do
– Alma Do, Commented Aug 5, 2014 at 8:39

Barmar · Accepted Answer · 2014-08-05 08:39:49Z

3

Use SUBSTRING_INDEX to get the parts of the range column.

WHERE score BETWEEN SUBSTRING_INDEX(rangecol, '-', 1) AND SUBSTRING_INDEX(rangecol, '-', -1)

answered Aug 5, 2014 at 8:39

Barmar

788k57 gold badges554 silver badges668 bronze badges

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

2 Comments

SUNny.K Over a year ago

thanks a lot man....u have saved my lots of time and my long code shorten to some lines...

szatti1489 Over a year ago

Yes, just one word: Brilliant! :)

Collectives™ on Stack Overflow

MYSQL query - checking if integer between range in single column string

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related