Afin varchar chaîne numérique

Est-il possible de commander des lignes de résultat par un varchar colonne en fonte à integer dans Postgres 8.3?

InformationsquelleAutor Jauzsika | 2011-12-14
  1. 96

    Il est tout à fait possible.

    ORDER BY varchar_column::int

    Assurez-vous d'avoir valide les littéraux entiers dans votre varchar colonne ou vous obtenez une exception. (Attaque et de fuite des blancs de l'espace est ok - il être coupé automatiquement.)

    Si c'est le cas, alors pourquoi ne pas convertir la colonne de integer pour commencer? Plus petit, plus rapide, plus propre, plus simple.

    Comment éviter les exceptions?

    Pour éliminer les chiffres avant la distribution, et ainsi éviter de possibles exceptions:

    ORDER BY NULLIF(regexp_replace(varchar_column, '\D', '', 'g'), '')::int

    • La regexp_replace() expression élimine efficacement tous les chiffres, de sorte que seuls les chiffres restent ou une chaîne vide. (Voir ci-dessous).

    • \D est une abréviation de la classe de caractères [^[:digit:]], ce qui signifie que tous les non-chiffres ([^0-9]).

      Dans le vieux Postgres versions avec l'ancienne configuration standard_conforming_strings = off, vous devez utiliser Posix échapper à la syntaxe de la chaîne de E'\\D' pour échapper à la barre oblique inverse \. C'était par défaut dans Postgres 8.3, de sorte que vous aurez besoin pour votre version obsolète.

    • Le 4e paramètre g est pour "globalement", demandant de remplacer tous occurrences, pas seulement la première.

    • Vous peut souhaitez autoriser l'un des principaux dash (-) pour les nombres négatifs.

    • Si la chaîne n'a pas de chiffres du tout, le résultat est une chaîne vide qui n'est pas valide pour une fonte à integer. Convertir des chaînes vides à NULL avec NULLIF. (Vous pourriez envisager de 0 à la place.)

    Le résultat est garanti pour être valide. Cette procédure est une fonte à integer comme demandé dans le corps de la question, pas pour numeric comme le titre le mentionne.

    Comment le faire rapidement?

    Une façon est d'une index sur une expression. (Lien vers le manuel de la version 8.3.)

    CREATE INDEX tbl_varchar_col2int_idx ON tbl
(cast(NULLIF(regexp_replace(varchar_column, E'\\D', '', 'g'), '') AS integer));

    Ensuite utiliser la même expression dans le ORDER BY clause:

    ORDER BY
cast(NULLIF(regexp_replace(varchar_column, E'\\D', '', 'g'), '') AS integer)

    Test avec EXPLAIN ANALYZE si l'index fonctionnel est utilisé.

    • Un moyen d'éviter que l'exception? 🙂
    • vous aurez à définir d'abord ce que le résultat doit être pour les valeurs non entières. Tri dernière? Ou de supprimer toute non-chiffres avant le cast?
    • Merci pour l'exception de la partie. Que regexp semble lent, ai-je le droit?
    • Certainement plus lent que la plaine de fonte, mais pas tant que ça. Le fait que vous avez à calculer les valeurs à tous est le principal lente ici-bas. Si vous voulez rapidement, soit convertir la colonne dans le tableau en entier, ou ajouter une colonne redondante qui détient le pré-calculé entier. Ensuite, vous pouvez l'indice de la colonne, ce qui permettra de beaucoup le plus rapide (à condition que la requête est simple et l'index peut être utilisé pour le tri). Ou l'utilisation d'un index sur une expression. J'ai modifié ma réponse.
    • Merci pour l'entraînement épuisantes :)).
    • Nous sommes ici pour apprendre. 🙂
    • ÉPIQUE COMMENTAIRE +1 !! Juste un ajout : E'\\D' a pas fonctionné pour moi, produit cette erreur > "org.postgresql.util.PSQLException: ERREUR: le type "ee" n'existe pas", alors je mets juste '\D' et a travaillé comme un charme. Je suis en utilisant postgresql9.3 version . merci encore!
    • Wow! +1 pour une réponse impressionnante. L'indice est incroyable. Je n'avais aucune idée que vous pourriez faire une telle magie.

    InformationsquelleAutor Erwin Brandstetter
  2. 4

    Également dans le cas où vous souhaitez commander par une colonne de texte qui a quelque chose convertibles à flotter, puis ce n':

    select * 
from your_table
order by cast(your_text_column as double precision) desc;
    InformationsquelleAutor Eric Leschinski