如何在运算符之间使用字符串类型的字段 [英] how to use between operator for string type of fields

问题描述

我有像''price varchar（30）''这样的表格字段



在该字段中我的值为



价格



10万美元

20万美元

13crores

15crores



现在我想根据用户的选择在运营商之间检索价值。（如果用户输入的价格为10lakhs，最大价格为20lakhs或minpprice：13crores和maxprice ：来自前端的15crores然后我需要显示上述价格之间的记录。）

I have tabel fields like ''price varchar(30)''

In that field i have values like

price

10lakhs
20lakhs
13crores
15crores

now i want to retrieve the values using between operator based on the user choice.(if user enter minprice as 10lakhs and maxprice as 20lakhs or minpprice:13crores and maxprice:15crores from frontend side then i need to display the records between above prices).

price between '10lakhs' and '20lakhs' and ((price like '__ crores') or (pprice like '__ lakhs'))

它显示的是十万卢比和十亿分的记录。



结果：

It is displaying both lakhs and crores records.

Result:

10lakhs
20lakhs
13crores
15crores

推荐答案

数据比较的规则之一：始终存储数值数字字段。



比较两个字符串时，它使用字符串比较，而不是数字字符串比较。这意味着逐个检查每个角色以查看哪个角色比另一个角色更高。因此，字符串比较将按以下顺序排序：

Rule one of data comparisons: always store numeric values in numeric fields.

When you compare two strings, it uses a string comparison, not a numeric one. This means each character is inspected one by one to see which is "higher" than the other. So a string comparison will be ordered as:

"1"
"10"
"11"
...
"2"
"20"
...

等等。



更改数据库。将数字存储在数字字段中，将日期存储在基于日期的字段中只在字符串字段中存储字符串值，否则您将遇到很多笨拙，烦人的错误，这些错误难以编码 - 完全没必要。

and so forth.

Change your database. Store numbers in numeric fields, and dates in date based field. Only ever store string values in string fields or you will get loads of awkward, annoying errors which are hard to code round - and completely unnecessary.

这篇关于如何在运算符之间使用字符串类型的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！