Continuando el post de ordenación natural añado un enlace al sitio oficial de PostgreSQL en el que un comentarista indica la forma de ordenar de forma natural las secuencias de caracteres que incluyen números. Y por si alguna vez desapareciese dicho comentario, copio las funciones aquí abajo.
CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$
SELECT CASE WHEN $1 ~ ‘[^0-9]+’ THEN
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM ‘[^0-9]+’))+1 ),
” )
ELSE
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM ‘[0-9]+’))+1 ),
” )
END
$$ LANGUAGE SQL;
CREATE FUNCTION btrsort(text, integer) RETURNS text AS $$
SELECT CASE WHEN $2-1>0
THEN
RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM ‘^[^0-9]+’), ”), 1, 12), 12, ‘ ‘) ||
LPAD(SUBSTR(COALESCE(SUBSTRING(btrsort_nextunit($1) FROM ‘^[0-9]+’), ”), 1, 12), 12, 0) || btrsort(btrsort_nextunit(btrsort_nextunit($1)), $2-1)
ELSE
RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM ‘^[^0-9]+’), ”), 1, 12), 12, ‘ ‘) ||
LPAD(SUBSTR(COALESCE(SUBSTRING(btrsort_nextunit($1) FROM ‘^[0-9]+’), ”), 1, 12), 12, 0)
END
;
$$ LANGUAGE SQL;
CREATE FUNCTION btrsort(text) RETURNS text AS $$
SELECT btrsort($1, 10);
$$ LANGUAGE SQL;
La forma de usarlas es la siguiente. Primero vemos la ordenación que no tiene en cuenta los números (se observa que devuelve 10 como si fuese menor que 8):
SELECT room_number FROM rooms ORDER BY room_number;
room_number
———–
10
8
9
9a
Para ordenar de forma natural:
SELECT room_number
FROM (SELECT room_number, btrsort(room_number) AS room_sort
FROM rooms
ORDER BY room_sort) AS foo
Lo que devuelve:
room_number
———–
8
9
9a
10
Quiero agradecerle al autor original de estas funciones porque me resolvió un verdadero problema en el trabajo. Gracias Ryan Hughes.
El enlace al sitio oficial con el comentario de Ryan.
