CODEWITHSUNDEEP
phpmysql
Avionicom
Avionicom

Reputation: 191

MySQL, price ordering

In a MySQL database, prices are stored in a way like this:

where the dot stands for thousand (italian currency, like comma in US). If I try to sort these data with "Order by price ASC" I have the following order:

It seems that it confuses the dot with the comma. Infact, 120 is the last but it should be the first. Is there a way to order the sequence correctly?

Upvotes: 3

Views: 2334

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

He is expecting to get this result:

120
10.000
20.000
25.000
25.000

Now,

select price from t
order by CAST(price  AS DECIMAL(10,2));

And:

select price from t
Order by price+0 ASC

Will both return this:

10.000
20.000
25.000
25.000
120

He will need to parse the field somehow:

select price from t
order by replace(price, '.', '')+0;

Which gives the expected result.

You can play with it here

Also note if you have decimals (which are separated by ',') you might have to use this query:

select price from t
order by replace(replace(price, '.', ''), ',', '.')+0;

Now, of course the idea is not to store them as text...

Upvotes: 2

spencercw
spencercw

Reputation: 3358

Yes. Don't store them as strings. You should use a DECIMAL(10, 2) instead, where 10 is the total number of digits it can store and 2 is how many of those are on the right hand side of the decimal point. You can adjust the parameters as appropriate, but storing them as strings isn't a very good idea.

Upvotes: 3

user319198
user319198

Reputation:

Seems price comumn is varchar.

You neeed to cast it in int float or double

ORDER BY CAST(price  AS DECIMAL(10,2));

or simple you can use below sort trick on order by clause

Order by price+0 ASC

Upvotes: 0

Related Questions