Mysql - Ordering by REPLACE() not working as expected-CodePudding

I have a table that stores denial codes. All the codes start with a D and are numerical after that. I have used select REPLACE(adcode,'D','') as denialCode from approved_denials where adactive is not null order by denialcode

This still orders like this:
| denialCode |
------------
| 1 |
| 10 |
| 11 |
| 14 |
| 16 |
| 17 |
| 18 |
| 19 |
| 2 |
| 20 |
| 21 |
| 23 |
| 25 |
| 3 |
| 30 |
| 4 |
| 5 |
| 6 |

Then i tried:
select adcode from approved_denials where adactive is not null order by REPLACE(adcode,'D','');

This output is:
--------
| adcode |
--------
| D1 |
| D10 |
| D11 |
| D14 |
| D16 |
| D17 |
| D18 |
| D19 |
| D2 |
| D20 |
| D21 |
| D23 |
| D25 |
| D3 |
| D30 |
| D4 |
| D5 |
| D6 |
--------

Is there any way i can strip the 'D' and sort these correctly EG:
D1
D2
D3
D4
D5

Etc...
Thanks.

CodePudding user response：

The return type from replace() in mysql is varchar, so it is sorted as strings - 12 comes before 2. If you want it sorted numerically, cast it to unsigned-

cast(replace(adcode, 'D','') as unsigned)