将 varchar 字符串排序为数字 [英] Order varchar string as numeric

问题描述

是否可以在 Postgres 8.3 中通过转换为 integer 的 varchar 列对结果行进行排序?

Is it possible to order result rows by a varchar column cast to integer in Postgres 8.3?

推荐答案

绝对有可能.

ORDER BY varchar_column::int

确保在您的 varchar 列中为每个条目提供有效的整数文字，否则您会收到异常 invalid input syntax for integer: ....(前导和尾随空格都可以 - 它会被自动修剪.)

Be sure to have valid integer literals in your varchar column for each entry or you get an exception invalid input syntax for integer: .... (Leading and trailing white space is ok - it will be trimmed automatically.)

如果是这样，那么为什么不将列转换为 integer 开始呢?更小、更快、更清洁、更简单.

If that's the case, though, then why not convert the column to integer to begin with? Smaller, faster, cleaner, simpler.

在强制转换之前删除非数字字符，从而避免可能的异常:

To remove non-digit characters before the cast and thereby avoid possible exceptions:

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

• regexp_replace() 表达式有效地删除了所有非数字，因此只保留数字或空字符串.(见下文.)

  • The regexp_replace() expression effectively removes all non-digits, so only digits remain or an empty string. (See below.)

    D 是字符类 [^[:digit:]] 的简写，意思是所有非数字([^0-9]).
    在带有过时设置的旧 Postgres 版本 standard_conforming_strings = off 中，您必须使用 Posix 转义字符串语法 E'\D' 来转义反斜杠 .这是 Postgres 8.3 中的默认设置，因此您需要在过时的版本中使用它.

    D is shorthand for the character class [^[:digit:]], meaning all non-digits ([^0-9]).
    In old Postgres versions with the outdated setting standard_conforming_strings = off, you have to use Posix escape string syntax E'\D' to escape the backslash . This was default in Postgres 8.3, so you'll need that for your outdated version.

    第四个参数 g 用于全局"，指示替换所有出现，而不仅仅是第一个.

    The 4th parameter g is for "globally", instructing to replace all occurrences, not just the first.

    您可能希望允许负数前导破折号 (-).

    You may want to allow a leading dash (-) for negative numbers.

    如果字符串根本没有数字，则结果是一个空字符串，该字符串对于转换为 integer 无效.使用 <将空字符串转换为 NULL代码>NULLIF.(您可以考虑使用 0 代替.)

    If the the string has no digits at all, the result is an empty string which is not valid for a cast to integer. Convert empty strings to NULL with NULLIF. (You might consider 0 instead.)

    保证结果有效.此过程用于按照问题正文中的要求转换为 integer，不适用于 numeric 作为标题提及.

    The result is guaranteed to be valid. This procedure is for a cast to integer as requested in the body of the question, not for numeric as the title mentions.

    一种方法是表达式索引.>

    One way is an index on an expression.

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

    然后在 ORDER BY 子句中使用相同的表达式:

    Then use the same expression in the ORDER BY clause:

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

    用EXPLAIN ANALYZE测试函数索引是否真正被使用.

    Test with EXPLAIN ANALYZE whether the functional index actually gets used.

    这篇关于将 varchar 字符串排序为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！