postgresql - que - Ordenar cadena varchar como numérica

replace en postgresql

Question

¿Es posible ordenar filas de resultados por un molde de columna varchar a integer en Postgres 8.3?

Answer 1

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 obsoleta standard_conforming_strings = off , debe usar la sintaxis de la cadena de escape de Posix E''//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 a NULL con NULLIF . (En su lugar, podría considerar 0 ).

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.

Answer 2

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;