需要检索字段在两个数值之间的记录 - 字段是varchar并且带有逗号的数字
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...
我有一个包含最小和最大字段的搜索表单。 它查询MySQL表。 p>
字段'List Price'是varchar，包含逗号的值，如200,000或34,150。 p>
我可以' t记录正确的记录... p>
（ （ （ （ 是否因为字段值是char而不是int？ 可以在mysql select中从字段中删除逗号吗？ （ List Price code> BETWEEN'100000'''101000'）我得到的记录是101900 - 高于最大值。 p>
List Price code> BETWEEN'100,000'和'101,000'）我得到的记录是＆gt; = min和＆lt; = max p>
List Price code>＆lt; ='101,000'）我得到的记录大约为100,000，但也有1,280,000和1,300,000 p>
List 价格 code>＆gt; ='300,000'）我得到的记录像399,900但也是70,000 p>
List Price code>＆gt; ='300,000'＆lt; - 以某种方式从号码中删除逗号，因此select会读取'List Price'＆gt; = 300000） p>
谢谢...... p>
div>
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
You should cast string to integer. There is no other way.
CAST(`list price` AS SIGNED) BETWEEN 100000 AND 101000
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.
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.