Is there any way to modify a column before it is ordered in MySQL?

Posted by George Edison on Stack Overflow See other posts from Stack Overflow or by George Edison
Published on 2010-03-19T23:59:03Z Indexed on 2010/03/20 0:01 UTC
Read the original article Hit count: 279

Filed under:
|
|
|

I have a table with a field value which is a varchar(255). The contents of the field can be quite varied:

  • $1.20
  • $2994
  • $56 + tax (This one can be ignored or truncated to $56 if necessary)

I have a query constructed:

SELECT value FROM unnamed_table ORDER BY value

However, this of course uses ASCII string comparison to order the results and does not use any numerical type of comparison.

Is there a way to truly order by value without changing the field type to DECIMAL or something else? In other words, can the value field be modified ('$' removed, value converted to decimal) on the fly before the results are sorted?

© Stack Overflow or respective owner

Related posts about mysql

Related posts about convert