MySql search for integers from a number with a dash

Question

I have a table in which I have one field with a dash. How...

I need to find this with a condition.

For example, if I have a single value of 25, then I need to look for entries that include a value of 25, like 20-31. There are 6 entries in the above image, which include 25 values. Therefore, he must return 6 records.

Please help me in this matter? What will be the request for this?

Answer 1

You can use MySQL substring_index () to easily get 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 records where a specific value is between the range:

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

+ 0 MySQL substring_index() .

Answer 2

, SUBSTRING_INDEX:

SELECT * 
FROM table_name 
WHERE 25 >= CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, '-', 1), '-', -1), UNSIGNED INTEGER)
  AND 25 <= CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, '-', 2), '-', -1), UNSIGNED INTEGER)

-- or

SELECT *
FROM table_name
WHERE 25 BETWEEN CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, '-', 1), '-', -1), UNSIGNED INTEGER)
             AND CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, '-', 2), '-', -1), UNSIGNED INTEGER)

demo: http://sqlfiddle.com/#!9/4ac7b3/3/0

---

. VARCHAR , INTEGER. ALTER TABLE:

ALTER TABLE table_name ADD colNameA INT;
ALTER TABLE table_name ADD colNameB INT;

, UPDATE:

UPDATE table_name SET
    colNameA = CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, '-', 1), '-', -1), UNSIGNED INTEGER),
    colNameB = CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, '-', 2), '-', -1), UNSIGNED INTEGER)

VARCHAR, ALTER TABLE:

ALTER TABLE table_name DROP COLUMN col_name

() :

SELECT * 
FROM table_name 
WHERE 25 >= colNameA AND 25 <= colNameB

-- or

SELECT *
FROM table_name
WHERE 25 BETWEEN colNameA AND colNameB

Answer 3

, MySQL.

But using php, you can check the range.

For instance,

// First of all get all record from database.
$search = 10; // Your searching value.
// Loop all rows.
while($rows = mysqli_fetch_array($r)){
    $explode = explode("-",$rows['dash']); // For get from-to value.
    $range = isset($explode[0])&&isset($explode[1])?range($explode[0],($explode[1]-1)):array(); // For get range.
    if(in_array($search,$range)){ // For check searching value is exist or not !
        echo "Yes ! I get into ".$rows['dash']; // Do stuff.
    }
}

Note: if 10-15, then it will be checked using 10,11,12,13,14.

Answer 4

For me, if you do not want to change the structure of the table, then

Just write down the notes according to your other condition. Then from this data check your sum between this field using foreach loop and explode. as

If you have $ data like all data

foreach($data as $value){
     $new_val=explode(',',$value['new_field']);
     if(25 >= $new_val[0] && 25 <= $new_val[1]){
          // here create new array
     }
}