SQLite 按价格排序不起作用
我有一个名为价格的列,所有值都是文本,如下所示:“$26.71”。 我想按降序排序此列,但我不能,因为该列未定义为 INTEGER 并且它在所有值前面都有一个“$”字符。 为了使这项工作我应该做什么? 谢谢。
I have a column called price and all the values are TEXT so like this: "$26.71".
I want to sort this column in descending order but i can't because the column is not defined as INTEGER and it has a "$" character in front of all the values.
What should i do in order to make this work??
Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您可以组合
ltrim
和cast
以获取实际的数字作为排序依据:注意,
ltrim
也如果您有多种货币,则有效。只需将它们全部排列起来,例如'$€'
。ltrim
从左侧删除所有字符,直到遇到不在该字符串中的字符。You can combine
ltrim
andcast
to get an actual numeric from which to sort by:Note,
ltrim
also works if you have multiple currencies. Just line them all up, like'$€'
.ltrim
removes any and all characters from the left side until it hits a character that's not in that string.Mehmet,理想情况下,您应该将价格存储为货币/小数/数字数据类型,并在 UI 上附加“$”。埃里克的解决方案将适用于您眼前的问题,但随着您的系统的增长,这些选择的性能将变得越来越差。
Mehmet, Ideally you should be storing the prices as a currency/decimal/numeric data type and appending the "$" on the UI. Eric's solution will work for your immediate problem, but as your system grows, the performance of these selects will get worse and worse.