[RESOLVED] SQL query, sort by price where price is varchar

Terminator

Hello,

We have a database where the price field is varchar (prices are stored as)

100,00
150,50
1.100,00

Etc etc, however now our sorting is not in the correct way.
Anyone got an idea howto solve this ?

Weedpacket

Terminator wrote:
Anyone got an idea howto solve this ?

Yes; use a numeric type to store numbers. There's no excuse for it to ever have been a varchar in the first place.

You might be able to order them numerically using the CAST() function in the order by clause, but I wouldn't guarantee that would work given the extra (and unnecessary) punctuation you've added to the values.

Terminator

Ok, but if I choose a numeric type.. Can I still store it like this 1.000,00 ?

Weedpacket

You shouldn't anyway. The punctuation is only cosmetic and not part of the value. Store the number as a number and format it as a string how you want when and if you display it.

bradgrafelman

For example, once you retrieved the number in its plain format (according to your DBMS), you could use PHP's [man]number_format/man to display it in a customizable format.

Terminator

Thanks Weedpacket & bradgrafelman!
Ver usefull info and this will solve my problem 🙂

PS : bradgrafelman, is that a custom title ? 😃

bradgrafelman

Heh, yes it is, and for the many months I've had it set that way, you're only the second person to inquire about it.

Terminator

Hehe, yeah I already noticed it but thought naaah don't want to botter him 🙂
However when replying I thought I just have to ask 😃

As said, thanks for resolving my question!