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

Question

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?

Answer 1

Create a second column without the $-sign, sort on that one and use the data of the original column in your application.

In order to create the helper column and sort on it you would need something like this:

SELECT value, CAST(SUBSTR(value, 2) AS UNSIGNED) sort_col
  FROM unnamed_table ORDER BY sort_col

Answer 2

You could sort on an expression made to "parse" the text into decimal

SELECT value FROM unnamed_table ORDER BY expression_returning_decimal(value)

Where your expression uses MySQL functions to extract the number from the string depending on what form you expect it to take (something like CAST(value AS DECIMAL(10,2)), but you'll probably need to deal with the extraneous non-numeric characters somehow as I'm not sure what the CAST will give if you don't strip them).