MySQL sorting string values with numbers?

Question

My data is like this:

KRYPTON-4xLV
KRYPTON-3xSTG
KRYPTON-16xTH
KRYPTON-8xTH
KRYPTON-8xLV

When I call ORDER BY on this column the values are sorted like this:

KRYPTON-16xTH
KRYPTON-3xSTG
KRYPTON-4xLV
KRYPTON-8xLV
KRYPTON-8xTH

Is there a way to sort this like?

KRYPTON-4xLV
KRYPTON-8xLV
KRYPTON-3xSTG
KRYPTON-8xTH
KRYPTON-16xTH

Here is the SQL fiddle: http://sqlfiddle.com/#!9/6d061/2

Giorgi Nakeuri · Accepted Answer · 2015-06-01 09:09:19Z

1

Try the following:

select code
from (select *, substring(code, LOCATE('-', code) + 1, length(code)) o from products )t
order by 
substring(o, LOCATE('x', o), length(code)),
cast(o as UNSIGNED)

edited Jun 1, 2015 at 9:09

answered Jun 1, 2015 at 8:09

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

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

4 Comments

Primoz Rome Over a year ago

Close but still not quite as expected. Here are the results of your edit: sqlfiddle.com/#!9/6d061/12. Not even sure if it's possible to sort the way I expect. See my question...

Giorgi Nakeuri Over a year ago

@PrimozRome, you are not sorting by numbers... Why 3 is between 2 eights and not the first?

Primoz Rome Over a year ago

It can be first, what I want to do is to keep "identical products" together sorted by number. For example KRYPTON-4xLV, KRYPTON-8xLV should sort one after another. Same goes for KRYPTON-8xTH should be followed by KRYPTON-16xTH. So modules that end with TH, LV, XYZ should be sorted together by number that comes before that (3x, 4x, 8x). I hope you understand my need?

Giorgi Nakeuri Over a year ago

@PrimozRome, ok I've got it.

Collectives™ on Stack Overflow

MySQL sorting string values with numbers?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related