How to query data in `ranges` between range where ranges are saved in DB in single column separated by `-`?

Admin 1
Viewed 49

I have following table ranges

id title range 
1  ABC   0-500
2  PQR   501-600
3  XYZ   601-700

PHP Code

$range_input = "0-550";
$data = DB::select("Select * from ranges where range between $range_input");

Expected output: I expect id 1 and 2 in results between 0-550.

Thanks

laravel mysql php
1 Answers

The design of your table is poor, and the lower and upper bounds of the range should be stored in separate columns (more on this later). If are really stuck with the current design, you could use SUBSTRING_INDEX to separate the lower and upper bounds:

SELECT *
FROM ranges
WHERE ? < SUBSTRING_INDEX(`range`, '-', -1) AND
      ? > SUBSTRING_INDEX(`range`, '-', 1)

To the ? placeholders, you may bind the range values for which you are searching, e.g. 0 and 550

Demo

Regarding your current design, it would be much better to store the lower and upper values of the range separately, that is, use this version of the table:

id title lower upper
1  ABC   0     500
2  PQR   501   600
3  XYZ   601   700

Now you only need the following simplified query:

SELECT *
FROM ranges
WHERE 0 < upper AND 550 > lower;
user_18632290
Related