Solución:
Un enfoque que puede adoptar es crear un naturalsort función para esto. Aquí hay un ejemplo, escrito por la leyenda de Postgres RhodiumToad.
create or replace function naturalsort(text)
returns bytea language sql immutable strict as $f$
select string_agg(convert_to(coalesce(r[2], length(length(r[1])::text) || length(r[1])::text || r[1]), 'SQL_ASCII'),'x00')
from regexp_matches($1, '0*([0-9]+)|([^0-9]+)', 'g') r;
$f$;
Fuente: http://www.rhodiumtoad.org.uk/junk/naturalsort.sql
Para usarlo, simplemente llame a la función en su orden de la siguiente manera:
SELECT * FROM employees ORDER BY naturalsort(em_code) DESC
La razón es que la cadena se ordena alfabéticamente (en lugar de numéricamente como le gustaría) y 1 se ordena antes de 9. Podrías resolverlo así:
SELECT * FROM employees ORDER BY substring(em_code, 3)::int DESC
Sería más eficiente eliminar el ‘EM’ redundante de su em_code – si puede – y para empezar, guarde un número entero.
Respuesta adicional a la pregunta en el comentario.
Para quitar todos y cada uno de los dígitos que no son dígitos de una cadena:
SELECT regexp_replace(em_code, E'\D','','g')
FROM employees
D es la abreviatura de clase de expresión regular para “no dígitos”.
'g' ya que el cuarto parámetro es el modificador “globalmente” para aplicar el reemplazo a cada ocurrencia en la cadena, no solo a la primera.
Así que reemplazo todos los que no son dígitos con la cadena vacía que destila únicamente los dígitos de la cadena.
Esto siempre surge en preguntas y en mi propio desarrollo y finalmente me cansé de las formas complicadas de hacer esto. Finalmente lo descompuse y lo implementé como una extensión de PostgreSQL:
https://github.com/Bjond/pg_natural_sort_order
Es de uso gratuito, licencia MIT.
Básicamente, solo normaliza los números (cero números pre-pendientes) dentro de las cadenas de modo que pueda crear una columna de índice para la clasificación a toda velocidad al natural. El archivo Léame lo explica.
La ventaja es que puede hacer que un disparador haga el trabajo y no el código de su aplicación. Se calculará a la velocidad de la máquina en el servidor PostgreSQL y las migraciones que agregan columnas se vuelven simples y rápidas.