Search code examples
mysqlsqlselectrangedenormalization

MySQL select value range within a value range, from a dash-separated column value?

How do I select a value range from a column where two values in it are separated by a dash, using MySQL?

Here's my example table named "example":
enter image description here

The user enters a low value (X) and a high value (Y).
For example X=2.5 and Y=7.2

I want to select all items where the left value is higher than X (in this case 2.5) and the right value is lower than Y (in this case 7.2). Using these X and Y values I should end up with the rows 2 and 5 as a result.

Sort of like this:

SELECT * FROM example WHERE MIN(value) > X AND MAX(value) < Y

How do I do this?

Solution

  • You can use LEFT and RIGHT functions to get X and Y out of your value field.

    So I think you are looking for something like this:

    SELECT * FROM example WHERE CAST(LEFT(value,3)AS DECIMAL(2,1)) > 2.5 and CAST(RIGHT(value,3)AS DECIMAL(2,1)) < 7.2