MySql: Order by Path (string)

Question

I need to order categories by path, following the adjacency pattern:

SELECT e.id_category 'id',
    p.tPath
FROM (category c)
LEFT JOIN `category_path` p ON c.id_category = p.id_category
ORDER BY p.tPath, c.id_category ASC 

The paths of categories are saved as TEXT column, the problem is when I need to sort IDs that have more of one digit:

16 has 1 as first digit, then is the first row within the /1/2/ category

It is possible to have this?:

Answer 1

Because you are storing numbers, it would be better to zero pad the numbers to a fixed length. But, that may not be feasible for multiple reasons. So, one method is to extract the components, convert each component to a number, and sort on those:

order by (case when path like '/%'
               then substring_index(substring_index(path, '/', 2), -1) + 0
               else -1
          end),
         (case when path like '/%/%'
               then substring_index(substring_index(path, '/', 3), -1) + 0
               else -1
          end),
         (case when path like '/%/%/%'
               then substring_index(substring_index(path, '/', 4), -1) + 0
               else -1
          end)

You can continue this for as many parts as might be in the string.