Mysql sort string as number-CodePudding

I have a field like this

    table A {text varchar(255) }

with entries like this

    text => A$123.34
    text => A$10.34
    text => $15.45

I want to sort those so they list as

    A$10.34
    $15.45
    A$123.34

I've tried many solutions posted here - cast to decimal, value * 1, value 0, etc. but they all fail. I did find one that comes very close:

    select text as num from Table A order by substring(num,2) desc

This gives

    A$10.34
    A$123.34
    $15.45

I know it is because the substring is looking past two characters and the last entry only has one but I can't see how to fix that. Does anyone know how to get this to sort as I want?

In most of the posts I've seen it said to just store the value as a number. That is not possible due to the amount of changes it would require in the project.

CodePudding user response：

Use SUBSTRING_INDEX():

mysql> select * from A order by 0   substring_index(text, '$', -1);
 ---------- 
| text     |
 ---------- 
| A$10.34  |
| $15.45   |
| A$123.34 |
 ----------