MySQL ORDER of alphanumeric strings, priority to numbers then trailing non numeric characthers

Question

I have this problem. A column in MySQL DB has data as following: 1 10 100 102 1020 1022 1023 1024 1025 1025A 1026A . . .

I Obtained correct numeric sorting with a query like this:

    SELECT DISTINCT LPAD(ubicazione, 8, '0') FROM t_ubicazione ORDER BY LPAD(ubicazione, 8, '0') ASC;

that give me this result:

00000001 00000017 00000018 00000019 0000001A 0000001B 0000001M 0000001S 00000021 00000022

My problem is that 0000001A should be after 00000001, so correct order should be:

how can I obtain this result?

forpas · Accepted Answer · 2021-03-25 12:01:24Z

1

You can get the numeric part of the column as an integer number with an implicit conversion ubicazione + 0 and sort by that first and then ubicazione:

SELECT ubicazione 
FROM t_ubicazione 
ORDER BY ubicazione + 0, ubicazione;

See the demo.

answered Mar 25, 2021 at 12:01

forpas

165k10 gold badges51 silver badges85 bronze badges

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

Comments

Keyur Buch · Accepted Answer · 2021-03-25 12:22:29Z

0

SELECT ubicazione FROM t_ubicazione ORDER BY CAST(ubicazione AS UNSIGNED), ubicazione

answered Mar 25, 2021 at 12:22

Keyur Buch

414 bronze badges

Collectives™ on Stack Overflow

MySQL ORDER of alphanumeric strings, priority to numbers then trailing non numeric characthers

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related