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

问题描述

是否可以通过 varchar 列在Postgres 8.3中将结果行转换为整数？

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

推荐答案

绝对有可能。

ORDER BY varchar_column::int

确保在中有有效的整数文字varchar 列或您得到一个例外。 （前导和尾随空格是可以的 - 它会自动修剪。）

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

如果是这种情况，那么为什么不将列转换为整数开头？更小，更快，更干净，更简单。

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] ）。
    
    在过时设置 standard_conforming_strings = off 的旧Postgres版本中，必须使用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 适用于global，指示替换所有次出现，而不仅仅是第一次出现。

    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.

    如果字符串根本没有数字，则结果是一个空字符串，对于整数的强制转换无效。使用 NULL 的rel = noreferrer> 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.)

    结果保证有效。此过程适用于问题正文中的 整数 ，不适用于 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.

    一种方法是索引关于表达。 （链接到手册版本8.3。）

    One way is an index on an expression. (Link to manual version 8.3.)

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

    然后在 ORDER BY 子句：

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

    用 EXPLAIN ANALYZE 是否实际使用了功能索引。

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

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