postgresql - que - Ordenar cadena varchar como numérica
replace en postgresql (2)
Es absolutamente posible.
ORDER BY varchar_column::int
Asegúrese de tener literales enteros válidos en su columna varchar
u obtienes una excepción. (El espacio en blanco anterior y posterior está bien; se recortará automáticamente).
Si ese es el caso, entonces, ¿por qué no convertir la columna en un integer
para empezar? Más pequeño, más rápido, más limpio, más simple.
¿Cómo evitar excepciones?
Para eliminar caracteres que no sean dígitos antes del lanzamiento y así evitar posibles excepciones:
ORDER BY NULLIF(regexp_replace(varchar_column, ''/D'', '''', ''g''), '''')::int
La expresión
regexp_replace()
elimina efectivamente todos los dígitos que no sean dígitos, por lo que solo quedan dígitos o una cadena vacía. (Vea abajo.)/D
es la abreviatura de la clase de caracteres[^[:digit:]]
, es decir, todos los que no son dígitos ([^0-9]
).
En las versiones antiguas de Postgres con la configuración obsoletastandard_conforming_strings = off
, debe usar la sintaxis de la cadena de escape de PosixE''//D''
para escapar de la barra diagonal inversa/
. Esto estaba predeterminado en Postgres 8.3, por lo que lo necesitará para su versión desactualizada.El 4 ° parámetro
g
es para "globalmente" , indicando reemplazar todas las ocurrencias, no solo la primera.Es posible que desee permitir un guion delantero (
-
) para los números negativos.Si la cadena no tiene ningún dígito, el resultado es una cadena vacía que no es válida para un
integer
. Convierta cadenas vacías aNULL
conNULLIF
. (En su lugar, podría considerar0
).
El resultado está garantizado para ser válido. Este procedimiento es para convertir a un integer
como se solicita en el cuerpo de la pregunta, no para el numeric
como el título menciona.
¿Cómo hacerlo rápido?
Una forma es un índice en una expresión . (Enlace a la versión manual 8.3)
CREATE INDEX tbl_varchar_col2int_idx ON tbl
(cast(NULLIF(regexp_replace(varchar_column, E''//D'', '''', ''g''), '''') AS integer));
Luego use la misma expresión en la cláusula ORDER BY
:
ORDER BY
cast(NULLIF(regexp_replace(varchar_column, E''//D'', '''', ''g''), '''') AS integer)
Pruebe con EXPLAIN ANALYZE
si realmente se usa el índice funcional.
¿Es posible ordenar filas de resultados por un molde de columna varchar
a integer
en Postgres 8.3?
También en caso de que desee ordenar por una columna de texto que tenga algo convertible para flotar, entonces esto lo hace:
select *
from your_table
order by cast(your_text_column as double precision) desc;