MySql search integer range from number with dash

I have table in that I have one field with dash value. Like…

I need to search this with between condition.

For example if I have one value 25 then I need to search the records which include the value 25 like 20-31. In above image there are 6 records which include 25 value. So it should return 6 records.

Please help me in this query ? What would be the query for that ?

Answer

You can use MySQL’s substring_index() function to easily get the data before and after the dash:

select substring_index(yourcolumn,'-',1) as `lower`, substring_index(yourcolumn,'-',-1) as `upper`
from yourtable

This way you can return the records where a certain value falls between the range:

select * from yourtable
where 25 between substring_index(yourcolumn,'-',1) + 0 and substring_index(yourcolumn,'-',-1) + 0

The + 0 forces MySQL to convert the result of substring_index() to a numeric value before the comparison.