Need to retrieve records where field is between two numeric values - field is varchar and has number with a comma

I have a search form with min and max fields. It queries a MySQL table.

The field 'List Price' is varchar and contains values with commas such as 200,000 or 34,150.

I can't retieve the correct records...

( List Price BETWEEN '100000' AND '101000' ) I get records that are 101900 - higher than the max.

( List Price BETWEEN '100,000' AND '101,000' ) I get records that are >= min and <= max

( List Price <= '101,000' ) I get records that are like 100,000 but also 1,280,000 and 1,300,000

( List Price >= '300,000' ) I get records that are like 399,900 but also 70,000

Is it because the field value is char instead of int? Can the comma be stripped from the field in the mysql select? ( List Price >= '300,000' <- somehow remove the comma from the number, so the select would read 'List Price' >= 300000 )

Thanks...

php
mysql

2012-04-05 14:46
by Bill

yes. it is because of the string property of column. use a replace query to replace all , and change the column to bigint - hjpotter92 2012-04-05 14:50

You should convert that varchar field to a decimal type, alowing proper numeric comparisons. You could force it to be numeric-ish by stripping out the commas and doing a CONVERT(), e.g.

CAST(REPLACE(`List Price`, ',', '') AS DECIMAL) BETWEEN 100000 AND 101000

2012-04-05 14:50
by Marc B

I ended up using your suggestion and it works great. It also solved the odd record retieved using Cast without Replace - Bill 2012-04-05 15:15

You should cast string to integer. There is no other way.

CAST(`list price` AS SIGNED) BETWEEN 100000 AND 101000

2012-04-05 14:49
by Jarosław Gomułka

Worked like a charm! Thanks Jarosław! When I used between 10,000 and 20,000 there was one odd record that was returned: 14,900,000...I can live with that - Bill 2012-04-05 15:05

I forgot about ',' in numbers. Try Mark B solution - Jarosław Gomułka 2012-04-05 15:12

What MySQL version? Does the List price colum have an index on it? Is this bug relevant?

For the conversion, see the manual:

If both arguments in a comparison operation are strings, they are compared as strings.

The only sensible solution would be to alter your schema (i.e. change the column type), otherwise use a cast in each query, which will be considerably slower.

2012-04-05 14:54
by CodeCaster

you are treating everything as strings.

Don't put your arguments in quotes then they are numbers and mySQL will convert the strings to numbers for you.

2012-04-05 14:56
by Dave Appleton