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

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":

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?

CodePudding user response：

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

CodePudding user response：

First you need to access your table in a fashion that only has one value per column. (Multiple values per column, like 3.5-7.5 happen to be a very common relational database design antipattern. They cripple both performance and clarity.)

This SQL subquery does the trick for pairs of values.

SELECT item_id, name, 
       0.0 SUBSTRING_INDEX(value, '-',1) first,
       0.0 SUBSTRING_INDEX(value, '-', -1) last
 FROM example;

The expression 0.0 something is a MySQL trick to coerce a value to be numeric.

Then use the subquery to apply your search criteria.

SELECT item_id, name, first, last
  FROM (       SELECT item_id, name, 
                      0.0 SUBSTRING_INDEX(value, '-',1) first,
                      0.0 SUBSTRING_INDEX(value, '-', -1) last
                 FROM example
       ) s
 WHERE first > 2.5
   AND last < 7.2;

Fiddle here.

In a comment you asked about the situation where you have more than two values in a single column separated by delimiters. See this. Split comma separated values in MySQL

Pro tip Don't put more than one number in a column in an RDBMS table. The next person to use the table will be muttering curses all day while trying to use that data.

Pro tip Use numeric data types, not VARCHAR(), for numbers.