Simulating Natural Sorting in MySQL with PHP
Essentially, you want to have a custom, non-trivial sorting order,
based on logic that doesn't exist in the table schema and not supported by standard SQL operators.

The general (and perhaps obvious) solution to this problem seems to be including the necessary data in the table schema explicitly.
In other words, adding a dedicated column to represent the sorting key seems a sensible solution.

Suppose it's possible to use some SQL operations to generate this sorting key on the fly.
The problem with that is that you would have to re-type the (potentially complicated) operation every time you want to select an ordered subset from the table.
An option to make that more workable is embedding the sorting key logic inside a view on the source table.
If this approach is possible,
then the right solution will depend on the impact on performance due to the recalculation of the sorting key column in the view every time you access it.