Is it possible get data with search data between values-CodePudding

I am having data in table USER_TABLE as following :

 ----------------- 
| USERS_RANGE     |
 ----------------- 
| 5-98            |
| 9854-98666620   |
| 54-986          |
| 1-20            |
| 10000-122222220 |
| 10-1222         |
 -----------------

My requirement hear:

Ex:- if i search "7" it should display as :

 ------------- 
| USERS_RANGE |
 ------------- 
| 5-98        |
| 1-20        |
 -------------

I tried ex:- select * from USER_TABLE WHERE USERS_RANGE <= '7'; and some more mathamatical things in java Is there any query to get data like this.

CodePudding user response：

We can get the required data using subquery approach and SUBSTRING_INDEX() function.

/* select all inside subquery and filter with WHERE*/
SELECT USERS_RANGE FROM (
    /* select all range and split the range between '-' */
    SELECT
        USERS_RANGE
        , SUBSTRING_INDEX(USERS_RANGE, '-', 1) AS `from`
        , SUBSTRING_INDEX(USERS_RANGE, '-', -1) AS `to` 
    FROM USERS_RANGE_TABLE
) A
WHERE A.from <= 7 AND A.to >= 7;

The subquery will select all USER_RANGE data, split the range and create a new column with alias from and to,
and the main query will select all inside subquery and filter the result with where by column from and to.

Hope this help answer your question.

Reference: