Mysql sort string as number

Question

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.

I would save the A$ (or any other type of prefix) in another column, then sort by this new column+amount and then concatenate both for output — Book Of Zeus
– Book Of Zeus, Commented Sep 30, 2021 at 16:10
Thanks for the suggestion but, as mentioned, that is not an option. — user3052443
– user3052443, Commented Sep 30, 2021 at 16:29
I tried adding the 0 + suggestion but it makes the list less sorted. I do appreciate the suggestion. — user3052443
– user3052443, Commented Sep 30, 2021 at 17:31

Bill Karwin · Accepted Answer · 2021-09-30 17:31:05Z

2

Use SUBSTRING_INDEX():

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

answered Sep 30, 2021 at 17:31

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3052443 Over a year ago

Thank you Bill. That mostly did it. There is one entry for $1,205.54 and that ends up either as the first result (ASC) or the last (DESC). Is there a simple fix for that? If not, it is fine as it is since I doubt there will be many entries over 999.

Bill Karwin Over a year ago

Maybe strip out the commas: substring_index(replace(text, ',', ''), '$', -1)

Collectives™ on Stack Overflow

Mysql sort string as number

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related