希望运行select查询以按大小筛选产品，但列PRODUCT_SIZE的数字后面有字符，例如“英寸”。 我试过下面的方法，但是没有结果。
SELECT * FROM `products` where Product_size BETWEEN '2 inches' AND '4.9 inches'
如何从查询中删除单词“inches”？
您可以使用replace()
SELECT * FROM products
where Category_List =1 and Category_List_2=7 and
Designed_For_Who LIKE '%%' and (RRP between '0' and '100000') and
Product_size BETWEEN cast(replace('2 inches',' inches','') as int) AND
cast(replace('4.9 inches',' inches','') as int)
ORDER BY Name ASC
您必须将字符串转换为数值。
对于您的情况，可以通过添加0来隐式完成此转换:
SELECT *
FROM `products`
WHERE
Product_size LIKE '%inches'
AND
Product_size + 0 BETWEEN '2 inches' + 0 AND '4.9 inches' + 0
请参阅简化演示。
