SQL query between range saved in one field (e.g: 1;10)

Question

I wonder if it's possible to make a SQL query between a range that is save in the database as:

1;10

Where 1 is the minimum and 10 the maximum of the range.

For example:

Let's say I have a column 'persons' and the data is saved as above. Now I would like to query all rows where 5 is between the saved range.

Is this possible?

So this range could be different for each row in your table? — Jorick Spitzen, Aug 14 '15 at 08:07
Don´t have mysql but princip (in pseudo code): .... where substr 1 (casted to int) > (>=) your value AND substr 2 (casted to int) < (<=) your value — Thomas Krojer, Aug 14 '15 at 08:13
I can't really see what problem you're trying to solve, but @JorickSpitzen's answer would seem to be the way forward. — Strawberry, Aug 14 '15 at 08:41
Is it a requirement that the range be saved in a single column, or could you have a RANGE_MIN and RANGE_MAX column? — Mackers, Aug 14 '15 at 08:57

score 3 · Answer 1 · edited May 23 '17 at 11:58

You could look at this post, which explains how to store tuples in a single MySQL field. It does however complicate things quite a lot.

I don't know much about your actual use case, but I would definitely try to split your min and max values before saving it to the database. Then, simply use a BETWEEN SQL statement

CREATE TABLE  `example` (
  `val` int(10) unsigned NOT NULL,
  `min` int(10) NOT NULL,
  `max` int(10) NOT NULL
);

SELECT * FROM example 
WHERE val BETWEEN min AND max;

I know you specifically said you wanted it saved in one column, but that is simply not what databases are designed to do. They are designed to hold raw data, and a list of values separated by a specific character, no matter how simple and straightforward is processed or formatted data.

If it is expected for the table to have huge data, one should prefer this answer — Maulik Shah, Aug 14 '15 at 08:49

Maulik Shah · Answer 2 · 2015-08-14T08:39:04.077

1

SQL Fiddle

DB Schema

create table person
(rng varchar(10));

 insert into person values ('1;10');
 insert into person values ('12;100');

Select Query

SELECT 
rng, cast(SUBSTRING_INDEX(rng, ';', 1) as unsigned) as rangestart,cast(SUBSTRING_INDEX(rng, ';', -1) as unsigned) as rangeend
from person
where 5 between cast(SUBSTRING_INDEX(rng, ';', 1) as unsigned) and cast(SUBSTRING_INDEX(rng, ';', -1) as unsigned)

Output

|  rng | rangestart | rangeend |
|------|------------|----------|
| 1;10 |          1 |       10 |

edited Aug 14 '15 at 08:39

answered Aug 14 '15 at 08:21

Maulik Shah

402
1
4
18

Nice! This will most likely become very slow for large datasets, since it has to do the substring calculations over and over and over. You will not be able to implement efficient indices. But it is what Lars asked for! – Jorick Spitzen Aug 14 '15 at 08:28
Yes. Agreed.For huge data, ideal solution is to have ranges stored in separate columns. But the question does not mention that the table is expected to be heavy. Besides, i trust wisdom of the questioner. – Maulik Shah Aug 14 '15 at 08:34

Nathan · Answer 3 · 2015-08-14T08:40:15.167

0

You could split it and check.

DECLARE @YourVariableInBetween INT;
SELECT PersonFullName
FROM PersonTable
WHERE  @YourVariableToCheck BETWEEN CAST((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(YourRange, ';', 1), ';', -1)) AS UNSIGNED)
AND  CAST((SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(YourRange, ';', 2), ';', -1)) AS UNSIGNED)

Hope this helps

edited Aug 14 '15 at 08:40

answered Aug 14 '15 at 08:34

Nathan

1,520
1
12
21

SQL query between range saved in one field (e.g: 1;10)

3 Answers3