Sorting by Price with SQL data type VARCHAR

Question

I´m sorting by Price, look:

  SELECT Price 
    FROM re2_listings 
ORDER BY Price asc

Result:

1.200.000,00
1.500.000,00
200,00
3.000,00

...but the correct way is:

200,00
3.000,00
1.200.000,00
1.500.000,00

Understand? How to do this?

Answer 1

$q1 = "select Price from re2_listings order by CAST(Price AS Float)  asc";

Answer 2

Obviously, VARCHAR is not the best datatype for the storage of numerical amounts. If you must use it, you need to CAST the price column to an appropriate numeric type in the SELECT and ORDER BY parts of your statement, or create a view containing the CAST and SELECT from the view.

The datatype to CAST to, and the syntax for CASTing are product-dependent.

Answer 3

first, it helps to know your SQL variant to answer your question.

If you still have the ability to control the schema, you are much better off using a numeric data type (e.g. int or float) for the column and formatting it as a price when it is displayed.

Answer 4

MySQL or SQL Server supports:

  SELECT t.price
    FROM (SELECT r.price
                 CAST(r.price AS DECIMAL(15,2)) 'dprice'
            FROM RE2_LISTING r) t
ORDER BY t.dprice

I casted the varchar column to the appropriate data type, and used the alias dprice so I could reference it for sorting. If you are sorting in ascending fashion, you don't need to specify ASC if you don't want to - it's the default.