Mysql query compare string (varchar) as integer

Question

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:-

TBD 374,500
Negotiation
299,000

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

Cleanse the prices in your database and store as a numeric datatype, with a separate flag column to identify "Negotiable", etc — Mark Baker
– Mark Baker, Commented Dec 10, 2012 at 10:25

Vaishu · Accepted Answer · 2012-12-10 10:52:24Z

5

Try this...

String values are "0". other values are sorted..

SELECT CAST(price AS UNSIGNED INTEGER) price 
FROM tt order by price desc

answered Dec 10, 2012 at 10:52

Vaishu

2,3733 gold badges23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

julp · Accepted Answer · 2012-12-10 12:13:47Z

0

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.

edited Dec 10, 2012 at 12:13

answered Dec 10, 2012 at 10:41

julp

4,0611 gold badge26 silver badges23 bronze badges

Comments

Ja͢ck · Accepted Answer · 2012-12-10 12:16:35Z

0

I think in PHP there is no function like that. You have to hard code it yourself for getting a suitable result.

edited Dec 10, 2012 at 12:16

Ja͢ck

174k39 gold badges269 silver badges316 bronze badges

answered Dec 10, 2012 at 10:30

Sree

5391 gold badge8 silver badges20 bronze badges

Comments

dkkumargoyal · Accepted Answer · 2012-12-10 12:49:18Z

0

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.

answered Dec 10, 2012 at 12:49

dkkumargoyal

5563 silver badges10 bronze badges

Collectives™ on Stack Overflow

Mysql query compare string (varchar) as integer

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related