I am not sure if there is a function within mysql query. I have a price table, and the column name is "price" and its type is VARCHAR(). I intentionally needed it as VARCHAR for many reason.
Now the problem is if I want to search products for maximum price of e.g. 100000, but in my column we have different types of prices, example below:-
I am looking for a function like intVal() which will first convert the field value itself to INTEGER and compare it. Below is my current statement in query:-
... AND price <= $maximum_price
may be something like this should be the solution : intVal(price) <= $maximum_price
any help or direction towards solutions is appreciated. cheers
I think in PHP there is no function like that. You have to hard code it yourself for getting a suitable result.
Maybe something like:
SELECT ... WHERE CAST(SUBSTRING_INDEX(REPLACE(price, ',', ''), ' ', -1) AS SIGNED) <= $maximum_price
But I agree with previous comment, you'd better to rethink your table.
Try this...
String values are "0". other values are sorted..
SELECT CAST(price AS UNSIGNED INTEGER) price
FROM tt order by price desc
look like you have various formats in the price column. I think you are looking to compare if value in column is "TBD 374,500" then compare 374500 to 100000
In this case simple integer casting will convert all prices started with char to 0. you have to create your own function in mysql to take care of all formats.