CODEWITHSUNDEEP
mysqlsql
user3909204
user3909204

Reputation:

MySql search integer range from number with dash

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

enter image description here

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 ?

Upvotes: 3

Views: 1989

Answers (5)

Optimaz Prime
Optimaz Prime

Reputation: 937

If you want to get values beween 35 and 39, you can use below query,

SELECT 
    *
FROM
    yourtable
WHERE
    35 && 39 
    BETWEEN SUBSTRING_INDEX(tablecolumn, '-', 1) + 0 AND 
SUBSTRING_INDEX(tablecolumn, '-', - 1) + 0

Upvotes: 0

Sebastian Brosch
Sebastian Brosch

Reputation: 43584

You can use the following solution using 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

I recommend you to change your table design. I would split the column using the VARCHAR datatype to two columns using the INTEGER datatype. You can add two new columns with the the following ALTER TABLE commands:

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

To split the values of you current column and update the values to the new columns you can use the following UPDATE command:

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)

At the end you can remove the VARCHAR column using this ALTER TABLE command:

ALTER TABLE table_name DROP COLUMN col_name

Now you can use the following (simple) query to get the expected results:

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

-- or

SELECT *
FROM table_name
WHERE 25 BETWEEN colNameA AND colNameB

Upvotes: 1

Shadow
Shadow

Reputation: 34234

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.

Upvotes: 4

David Coder
David Coder

Reputation: 1138

According to me if you dont want to change the table structure then,

Just fetch the records as per your other condition, Then from that data check your amount between that field using foreach loop and explode. like

If you have $data as 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
     }
}

Upvotes: -2

Jaydeep Mor
Jaydeep Mor

Reputation: 1703

I don't know how it possible with MySQL.

But using php it possible to check with range.

For e.g.

// 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 check with 10,11,12,13,14.

Upvotes: -2

Related Questions