SUBSTRING_INDEX sort the number with the symbol and the character

Question

I am used MySQL 5.5 to do my query. I am facing the problem to sort the number with - in front of the character. Below is my example table and I want to sort the column number_with_name with the condition:

Table name: test123

+-----+--------------------+
| id  | number_with_name   |
+-----+--------------------+    
| 1   |   200-2 David      |
| 2   |   200-2-2 Peter    |
| 3   |   200-2-2-9 James  |
| 4   |   200 Robert       |
| 5   |   200-2-3 Siva     |
| 6   |   200-2-5 Denny    |
| 8   |   200-2-9 Rose     |
| 9   |   200-3 Kiki       |
| 10  |   100-3-2 Viva     |
| 11  |   100-3-15 Proton  |
| 12  |   100-3-6 Saga     |
| 13  |   100 Liver        |
| 14  |   100-3 Shawn      |
| 15  |   100-3-5-1 Kola   |
| 16  |   100-3-5-8 Frankie|
| 17  |   100-3-5 Jala     |
+----+---------------------+

I want the expected result like below the table:

+-----+--------------------+
| id  | number_with_name   |
+-----+--------------------+  
| 13  |   100 Liver        |
| 14  |   100-3 Shawn      |
| 10  |   100-3-2 Viva     |
| 17  |   100-3-5 Jala     |
| 15  |   100-3-5-1 Kola   |
| 16  |   100-3-5-8 Frankie|
| 12  |   100-3-6 Saga     |
| 11  |   100-3-15 Proton  |
| 4   |   200 Robert       |
| 1   |   200-2 David      |
| 2   |   200-2-2 Peter    |
| 3   |   200-2-2-9 James  |
| 5   |   200-2-3 Siva     |
| 6   |   200-2-5 Denny    |
| 8   |   200-2-9 Rose     |
| 9   |   200-3 Kiki       |
+----+---------------------+

I have used below the SQL to sort, but it doesn't work.

SELECT * from test123 order by SUBSTRING_INDEX(number_with_name, '-', -1) + 0 asc

Akira answer result for real test:

Hope someone can guide me on how to sort like this case. Thanks.

Answer 1

I think this is a more generalizable approach. The idea is to treat the components as strings rather than as numbers. You can order numeric strings as numbers by first ordering on the length and then on the value alphabetically.

Because prefixes don't matter when sorting strings, you can do this with just substring_index():

SELECT *
FROM test
ORDER BY LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 1)) ASC,
         SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 1),
         LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 2)) ASC,
         SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 2),
         LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 3)) ASC,
         SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 3),
         LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 4)) ASC,
         SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, ' ', 1), '-', 4)

This sorts up to four components. The only trick here is the nested substring_index(). This limits the comparison only to the initial number, which seems to be the intent.

Here is a db<>fiddle.

Answer 2

SELECT *
FROM test
ORDER BY SUBSTRING_INDEX(number_with_name, '-', 1) + 0
       , CASE WHEN LENGTH(number_with_name) - LENGTH(REPLACE(number_with_name, '-', '')) > 0
              THEN SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, '-', 2), '-', -1) + 0
              ELSE 0 
              END
       , CASE WHEN LENGTH(number_with_name) - LENGTH(REPLACE(number_with_name, '-', '')) > 1
              THEN SUBSTRING_INDEX(SUBSTRING_INDEX(number_with_name, '-', 3), '-', -1) + 0
              ELSE 0 
              END

fiddle