postgresql 将 varchar 字符串排序为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8502505/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order varchar string as numeric
提问by Jauzsika
Is it possible to order result rows by a varcharcolumn cast to integerin Postgres 8.3?
是否可以通过Postgres 8.3 中的varchar列对结果行进行排序integer？
回答by Erwin Brandstetter
It's absolutely possible.
这是绝对可能的。
ORDER BY varchar_column::int
Be sure to have valid integer literals in your varcharcolumn or you get an exception. (Leading and trailing white space is ok - it will be trimmed automatically.)
确保您的varchar列中有有效的整数文字，否则会出现异常。（前导和尾随空白是可以的 - 它将被自动修剪。）
If that's the case, though, then why not convert the column to integerto begin with? Smaller, faster, cleaner, simpler.
如果是这样，那么为什么不将列转换integer为开始呢？更小、更快、更清洁、更简单。
How to avoid exceptions?
如何避免异常？
To remove non-digit characters before the cast and thereby avoid possible exceptions:
在强制转换之前删除非数字字符，从而避免可能的异常：
ORDER BY NULLIF(regexp_replace(varchar_column, '\D', '', 'g'), '')::int
The
regexp_replace()expression effectively removes all non-digits, so only digits remain or an empty string. (See below.)\Dis shorthand for the character class[^[:digit:]], meaning all non-digits ([^0-9]).
In old Postgres versions with the outdated settingstandard_conforming_strings = off, you have to use Posix escape string syntaxE'\\D'to escape the backslash\. This was default in Postgres 8.3, so you'll need that for your outdated version.The 4th parameter
gis for "globally", instructing to replace alloccurrences, not just the first.You maywant to allow a leading dash (
-) for negative numbers.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 toNULLwithNULLIF. (You might consider0instead.)
该
regexp_replace()表达式有效地删除了所有非数字，因此只保留数字或空字符串。（见下文。）\D是字符类的简写[^[:digit:]]，意思是所有非数字 ([^0-9])。
在具有过时设置的旧 Postgres 版本中standard_conforming_strings = off，您必须使用 Posix 转义字符串语法E'\\D'来转义反斜杠\。这是 Postgres 8.3 中的默认设置，因此您需要在过时的版本中使用它。第四个参数
g用于"globally"，指示替换所有出现的，而不仅仅是第一个。您可能希望允许
-负数前导破折号 ( )。如果字符串根本没有数字，则结果是一个空字符串，对于强制转换无效
integer。将空字符串转换为NULLwithNULLIF。（你可以考虑0代替。）
The result is guaranteed to be valid. This procedure is for a cast to integeras requested in the body of the question, not for numericas the title mentions.
结果保证有效。此过程是针对integer问题正文中的要求进行强制转换的，而不是numeric如标题所述。
How to make it fast?
怎么做快？
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));
Then use the same expression in the ORDER BYclause:
然后在ORDER BY子句中使用相同的表达式：
ORDER BY
cast(NULLIF(regexp_replace(varchar_column, '\D', '', 'g'), '') AS integer)
Test with EXPLAIN ANALYZEwhether the functional index actually gets used.
测试EXPLAIN ANALYZE是否真正使用了功能索引。
回答by Eric Leschinski
Also in case you want to order by a text column that has something convertible to float, then this does it:
另外，如果您想按具有可转换为浮动的内容的文本列进行排序，则可以这样做：
select *
from your_table
order by cast(your_text_column as double precision) desc;
