SQLite sort by price doesn't work

Question

Hi everyone. 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.

Answer 1

You can combine ltrim and cast to get an actual numeric from which to sort by:

select * from table order by cast(ltrim(price, '$') as numeric) desc

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.

Answer 2

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.