php - mysql varchar字段上的条件之间

Question

我在房地产开发网站上工作。特性有两种价格，一种是价格，另一种是价格。

我将其存储为带有连字符的 varchar 字段，如下所示:- 25000-30000。

在搜索记录时，我在条件之间使用了这样的价格 where price between 5000 AND 25000。

执行此查询后，它会向我返回具有确切价格的特性。我无法获得中间结果。

Answer 1

Here you will need to differentiate 2 values by - and treat them as mathematical values rather than char. So your query will be like this.

SELECT * from tbl_property WHERE price between 5000 AND 25000 OR ( CAST( SUBSTRING_INDEX(  price,  '-', 1 ) AS UNSIGNED ) between 5000 AND 25000 ) OR ( CAST( SUBSTRING_INDEX(  price,  '-', -1 ) AS UNSIGNED )  between 5000 AND 25000 )