MySQL query with alphanumeric order

Question

I'm trying to get a list of some products' model & all models are alphanumeric.

I tried

ORDER BY CAST(field_name AS UNSIGNED)

and

ORDER BY field_name + 0

and

ORDER BY LENGTH(field_name)

and some other ways.

They works for most of them but there are some values that doesn't match the order.

The result I get is like

EAG-75

EAG-110

...

ESCG-500

ESCG-600

...

EYG-40

EYG-55

...

EMG-440

EMG-20

EMG-27

...

EAG-100

...

I don't understand what is causing this.

Please help.

Thanks in advance

how do you want them sorted? give an example. If you order alphabetically, EAG-110 comes before EAG-75 — Marcelo Diniz
– Marcelo Diniz, Commented Jan 24, 2012 at 15:27
I want them to be sorted alphanumericcaly like EAG-75 , EAG-100 , EAG-110 , EMG-20... — Onur Kucukkece
– Onur Kucukkece, Commented Jan 24, 2012 at 15:33
@Onur: I think you actually means "per non-numeric prefix, sort numerically" — gbn
– gbn, Commented Jan 24, 2012 at 15:38

gbn · Accepted Answer · 2012-01-24 15:35:23Z

5

You need to sort the 2 bits separately if you want a correct numeric sort per non-numeric prefix

ORDER BY
    -- sort by prefix only
    LEFT(MyCol, INSTR(col, '-')-1),  
    -- sort numerically within prefix
    CAST(SUBSTRING(MyCol, INSTR(col, '-')+1) AS UNSIGNED)

answered Jan 24, 2012 at 15:35

gbn

434k84 gold badges602 silver badges690 bronze badges

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

3 Comments

Sam Over a year ago

As long as all your model numbers are in the format AAA-##, you won't get much better than this solution.

Onur Kucukkece Over a year ago

Hi, it works perfect in that format but if there are some other results like AAAA# in the same query, then it doesn't work well for 4 character results, how can I fix it? thanks in advance

Onur Kucukkece Over a year ago

oh I think I fixed it by changing -1 to -2 in LEFT(MyCol, INSTR(col, '-')-1)

Sam · Accepted Answer · 2012-01-24 15:35:06Z

0

Would ORDER BY LENGTH(field_name), field_name work better for you?

answered Jan 24, 2012 at 15:35

Sam

10.2k12 gold badges72 silver badges105 bronze badges

Collectives™ on Stack Overflow

MySQL query with alphanumeric order

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related