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

Question

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?

@Alexey I am using MySQL (edited my question) – Aug 09 '22 at 12:16 — , Aug 09 '22 at 12:16

Swedo · Accepted Answer · 2022-08-09T12:31:18.217

0

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

edited Aug 09 '22 at 12:31

answered Aug 09 '22 at 12:18

Swedo

137
10

This is perfect, thanks a lot! Is there also a way to do this with 3 or more dash-separated values? I figured there is no such thing as MIDDLE() in this case, is there? – Aug 09 '22 at 12:33
1

@3141592653 For sure! I don't work with MySQL though but I found a function called SUBSTRING_INDEX. Here you can split the string based on the delimiter (-). By working with this you don't need to know how long the values are. And there is also a 3rd parameter to the function in which you can specify the number of results. https://www.w3schools.com/sql/func_mysql_substring_index.asp Hope it helps! – Swedo Aug 09 '22 at 12:41
1

@3141592653 It's a bit weird, but I was able to get this example to work to get the middle of the string seperated by dots. SELECT SUBSTRING_INDEX(SUBSTRING_INDEX("www.w3schools.com", ".",2),".",-1); Result: w3Schools. Here you're basically first making the string look like www.w3schools with the inner Substring function, and then using substring again to get the right side using the 3rd parameter (-1). – Swedo Aug 09 '22 at 12:48
If the value is `10.1-10.2` this suggested query will fail. – O. Jones Aug 09 '22 at 12:54
1

@O.Jones I am basing off of the example data. But yes, then just change the precision as explained in the documentation. E.g. DECIMAL(3,1) for 10.1 – Swedo Aug 09 '22 at 13:02
@O.Jones Good to know this! – Aug 09 '22 at 13:07

O. Jones · Answer 2 · 2022-08-09T12:59:00.370

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.

Ahh, this basically returns two values as if they were separate columns. Thanks for taking the time to make a fiddle as well. — , Aug 09 '22 at 16:13

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

2 Answers2