Is there any way to modify a column before it is ordered in MySQL?
- by George Edison
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?