User-defined function sorting column problem

I have taken reference from the internet about one user-defined function to locate 'nth occurrence of a string to do the sort column name in the database. I am using MySQL 5.5 version, not the latest version. Here is my sample database link https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=bcb32a6b47d0d5b061fd401d0888bdc3

My problem is I want to sort column name in the database follow the prefix number, but I am using below the SQL query, it doesn't work.

select t.id,t.name
from
(
select t.*, cast((case when col1_col2_ref > 0 
                      then 
                        substring_index(modified_name,'-',1)
                  else 
                     modified_name
                  end
                  ) as unsigned) col1
          , cast((case when    col1_col2_ref > 0
                           and col3_ref > 0
                       then 
                          substr(modified_name,(col1_col2_ref + 1),(col3_ref - (col1_col2_ref + 1)))
                       when col1_col2_ref > 0
                       then 
                         substr(modified_name,(col1_col2_ref + 1))
                  end) as unsigned) col2
          , cast((case when    col3_ref > 0
                           and col4_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1),(col4_ref - (col3_ref + 1)))
                       when col3_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1))
                  end) as unsigned) col3
          , cast((case when col4_ref > 0
                        then 
                         substr(modified_name,(col4_ref + 1))
                   end) as unsigned) col4
from
(
select t.*,substring_index(name,' ',1) modified_name
          ,locate('-',name,1) col1_col2_ref
          ,locate('/',name,1) col3_ref
          ,locate('/',name,locate('/',name,1)+1) col4_ref
  from filing_code_management t
) t
) t
order by col1,col2,col3,col4

It shows me below the result, it cannot sort properly.

Output 1

Actually I want the output sample like below:

Output 2

Output 3

This is before I can sort the column name link, https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=6b12a4d42359cb30f27a5bfb9d0c8210. After I am inserted into new data, it cannot work for me. Maybe an example in new data like this error (R)100-6-2-2 Mesyuarat Majlis Kerajaan Negeri (MMKN) JKK if I put () in front. Or in new data like this error 100-1-1 Penggubalan/Penyediaan/Pindaan Undang-Undang/Peraturan if I put / in between the word.

Hope someone can guide me to solve this problem. Thanks.

Solution

You should be able to adapt the following code to your needs (tested at your DB Fiddle!). I've used the file_name column instead of the name column to slightly simplify building the sort fields, as it seems the file name is always repeated in the first part of the name field anyway.

This would be quite a bit simpler using regular expression support, but I note that the version of MySQL you are using doesn't have this feature (I think it arrives in SQL 8.0, if I'm not mistaken).

SELECT id, 
    num_hyphens, 
    CAST(SUBSTRING_INDEX(CONCAT(file_name_adj,'-'), '-', 1) AS UNSIGNED) AS sort1,
    CAST(CASE WHEN num_hyphens = 0 
          THEN '0' 
          ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(file_name_adj,'-', 2), '-',-1) 
          END AS UNSIGNED) AS sort2,
    CAST(CASE WHEN num_hyphens <= 1 
          THEN '0' 
          ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(file_name_adj,'-', 3), '-',-1) 
          END AS UNSIGNED) AS sort3,
    CAST(CASE WHEN num_hyphens <= 2 
          THEN '0' 
          ELSE SUBSTRING_INDEX(file_name_adj, '-', -1) 
          END AS UNSIGNED) AS sort4,
    file_name,
    name
FROM (
SELECT id, name, MID(file_name, instr(file_name, ')') + 1) AS file_name_adj, file_name, 
        LENGTH(file_name) - LENGTH(REPLACE(file_name, '-', '')) AS num_hyphens 
FROM filing_code_management
) t1
ORDER BY sort1, sort2, sort3, sort4