如何用逗号来解析desc mysql的价格

$query = "SELECT * FROM mprice where price BETWEEN '1,000' AND '3,250' ORDER BY CAST( 
REPLACE(price,',', '.') AS decimal(10,2)) LIMIT $from, $max_results"

This query is showing result like this

1,000
2,000
3,250
10,900
11,700
27,600

and I want like this

1,000
2,000
3,250
 only

How can I do this? Please help me to fix this issue.

Thanks

This is somewhat bizarre functionality. I would recommend revisiting your schema design, if you have an option to do so, and utilizing a numeric type for your price column (you can include localization information in another column). That said, you can get it to work using the following query:

SELECT
    *
FROM
    mprice
WHERE
    CAST(REPLACE(price, ',', '.') AS decimal(10,2)) BETWEEN 1.00 AND 3.25
ORDER BY
    CAST(REPLACE(price, ',', '.') AS decimal(10,2))

Note that in your code you were attempting to restrict price to values between '1,000' and '3,250'. However, string comparison doesn't work well with numeric types, as it sorts lexicographically (alphabetically), treating numbers just like any other character. In this case, it was including values such as 10,900 and 27,600 because the first digit of these strings was between 1 and 3, which are the first characters of '1,000' and '3,250'.

When you cast the prices to numbers, you can correct this behavior. However, you also need to convert your filtering values to numbers, in this case 1.00 and 3.25.