Sorting on Mysql by string and number

Question

I have 3 fields such as title, name and size I am trying to sort string column (containing numbers).

+-----------------+--------------+-------------------+
+----- title -----+---- name ----+-------size--------+
+-----------------+--------------+-------------------+
+     SPR-235     + SPR 235      + 118 x 118 x 43 mm +
+     SPR-355-D   + SPR 355 D    + 140 x 140 x 41 mm +
+     SPR-355-K   + SPR 355 K    + 140 x 140 x 41 mm +
+     SPR-415     + SPR 415      + 155 x 155 x 50 mm +
+     SPR-455-K   + SPR 455 K    + 138 x 138 x 64 mm +
+     SPR-455-D   + SPR 455 D    + 138 x 138 x 64 mm +
+     SPR-135     + SPR 135      + 60 x 60 x 82 mm   +

I used this query:

SELECT title,name FROM table ORDER BY CAST(SUBSTRING(name,LOCATE(' ',name)+1) AS SIGNED)

It works fine like this:

+-----------------+--------------+
+----- title -----+---- name ----+
+-----------------+--------------+
+     SPR-135     + SPR 135      +
+     SPR-235     + SPR 235      +
+     SPR-355-D   + SPR 355 D    + <-- see
+     SPR-355-K   + SPR 355 K    + <-- see
+     SPR-415     + SPR 415      +
+     SPR-455-D   + SPR 455 D    + <-- see
+     SPR-455-K   + SPR 455 K    + <-- see

but when I change the query into:

SELECT * FROM table ORDER BY CAST(SUBSTRING(name,LOCATE(' ',name)+1) AS SIGNED)

Unfortunatelly, the "D" and "K" was failed to sort. And show like this:

+-----------------+--------------+-------------------+
+----- title -----+---- name ----+-------size--------+
+-----------------+--------------+-------------------+
+     SPR-135     + SPR 135      + 60 x 60 x 82 mm   +
+     SPR-235     + SPR 235      + 118 x 118 x 43 mm +
+     SPR-355-D   + SPR 355 D    + 140 x 140 x 41 mm + <-- see
+     SPR-355-K   + SPR 355 K    + 140 x 140 x 41 mm + <-- see
+     SPR-415     + SPR 415      + 155 x 155 x 50 mm +
+     SPR-455-K   + SPR 455 K    + 138 x 138 x 64 mm + <-- see
+     SPR-455-D   + SPR 455 D    + 138 x 138 x 64 mm + <-- see

I want to sort "D" first then "K". Big thanks to help this problem :)

Prix · Accepted Answer · 2013-07-13 04:33:02Z

2

Get the number part then get the last char from the string and order by both in sequence should work.

  SELECT name 
    FROM myTable 
ORDER BY CAST(SUBSTRING(name,LOCATE(' ',name)+1) AS SIGNED), 
         SUBSTRING(name,-1);

Live DEMO.

edited Jul 13, 2013 at 4:33

answered Jul 13, 2013 at 4:11

Prix

19.6k16 gold badges79 silver badges136 bronze badges

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

1 Comment

Prix Over a year ago

@RiaD thanks a lot for pointing out my mistake ;) it lead me to test it to make sure when it would fail and if it would be viable to his needs.

LoztInSpace · Accepted Answer · 2013-07-13 04:35:33Z

-1

Add another clause to your order by including the substring at character 9 (if it exists).

ORDER BY CAST(SUBSTRING(name,LOCATE(' ',name)+1) AS SIGNED), 
         SUBSTRING(name,9,1)

answered Jul 13, 2013 at 4:35

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

4 Comments

Prix Over a year ago

What if the number is bigger than 3 digits ? 9 would fail.

LoztInSpace Over a year ago

Sure, but I don't know anything about the data. The key take-aways were a) that you can sort by more than one thing and b) the syntax for doing it.

Prix Over a year ago

You don't need to know the data to know how to get the last char of it however with your example it would not work if for example the number is bigger. That's why I use SUBSTRING(name,-1) for instance as it will capture the last char.

Choco Spirit L'arc Over a year ago

Yup, I think that 'SUBSTRING(name,-1)' really helpful to get the last char. Million thanks for all :)

Collectives™ on Stack Overflow

Sorting on Mysql by string and number

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related