ORDER BY price with $ sign and commas

I have a field in my database named "price" and it setup as varchar. It contains dollar sign as well as commas.

The values in my database are like this:

$100,000

$625,005

$115,990

$2,450,000

$137,005

and I would like it to order it like this:

$100,000

$115,990

$137,005

$625,005

$2,450,000

I tried ORDER BY 0+price and ORDER BY ABS(price) but they just outputted in the order it was in the database. Is there anyway to order th开发者_开发问答is while keeping the field varchar

---

If at all possible, change your database to hold those values in a int, float or decimal field, depending on how much precision you need. Add the $ and all other formatting when outputting the values.

Everything else is just duct-taping around a bad database structure. It's not impossible, but it should be the very last resort when there is absolutely no way to change the database.

---

Fully agreeing with the above posts regarding the database design, there is a bad way anyways:

SELECT REPLACE(REPLACE(price,',',''),'$','') as cleanPrice FROM Table ORDER BY cleanPrice

The query has to do the replacements on every single row and therefore might become very slow..

---

Honestly you should really store these as int's and format them in PHP when you bring them out of the database, that way it's easier to work with the data and you can manipulate them as INT's.

When you pull them out you can use numeric_format to auto-add the comma's and then just add a $ infront of the price.

If you are storing multiple types of price's you can store a Currency Type in the DB too; which in this case would be USD.

---

I did this in Oracle quickly, so it might not work in MySQL...if not, I'm sorry. I'm only selecting the prices

select price
from table
order by (cast(substr(replace( replace('price', ',', ''), '"', '' ), 2, length(replace( replace('price', ',', ''), '"', '' ))-1) as int)

---

If you don't have any way of changing the DB structure you can add a computed column to the table for sorting purposes.

Unfortunately, mysql does not support computed columns, so after you create the new column you would need to add triggers (on UPDATEs and INSERTs) to compute the value for every inserted/changed row on the table.

In your trigger you would use REPLACE(REPLACE(price,'$',''),',','') for the value of the new column.

You could also create a view that has this logic in it and select from that.

The mysql documenation for creating triggers is located here

The mysql documenation for using views is located here