PostgreSQL ORDER BY issue - natural sort

The reason is that the string sorts alphabetically (instead of numerically like you would want it) and 1 sorts before 9. You could solve it like this:

SELECT * FROM employees ORDER BY substring(em_code, 3)::int DESC

It would be more efficient to drop the redundant 'EM' from your em_code - if you can - and save an integer number to begin with.

To strip any and all non-digits from a string:

SELECT regexp_replace(em_code, E'\\D','','g')
FROM employees

\D is the regular expression class-shorthand for "non-digits".
'g' as 4th parameter is the "globally" switch to apply the replacement to every occurrence in the string, not just the first.

So I replace every non-digit with the empty string distilling solely digits from the string.