CODEWITHSUNDEEP
mysqlsorting
Choco Spirit L'arc
Choco Spirit L'arc

Reputation: 27

Sorting on Mysql by string and number

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 :)

Upvotes: 1

Views: 1095

Answers (2)

LoztInSpace
LoztInSpace

Reputation: 5697

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)

Upvotes: -1

Prix
Prix

Reputation: 19528

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.

Upvotes: 2

Related Questions