Mysql sort string as number

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Use SUBSTRING_INDEX():

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


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x