mysql - Treat as number and search in between -

in table there column type varchar. , value in rows this:

 +------------+----+  |   price    | id |  +------------+----+  | rp 100000  | 1  |  | rp 200000  | 2  |  | rp 50000   | 3  |  | rp 1000    | 4  |  | rp 1000    | 5  |  | rp 2000    | 6  |  | rp 35000   | 7  |  | rp 45000   | 8  | 

i tried query

  select table price between rp 100000 , rp 200000 

but result it still fetch row not in between range, such selects id 1,2,4,5,6.

i think caused of 0 value query try ignore. , think solve treating var-char number (price/amount in rupiah) query recognize trying do.

can how achieve this? in advance.

try way:

select * yourtable  substring(price,3) between 100000 , 200000 

see here: http://sqlfiddle.com/#!9/37197/4

you don't need cast value implicit done.

what substring(price,3) doing getting third character of column price therefore without rf character (note blank space) , comparing numbers on between operator.

the substring(price,3) return string since composed numbers mysql on comparing numbers implicit convert it.