PostgreSQL 将带逗号的字符串转换为整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18882942/
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
PostgreSQL convert a string with commas into an integer
提问by Aaron Kreider
I want to convert a column of type "character varying" that has integers with commas to a regular integer column.
我想将具有带逗号的整数的“字符变化”类型的列转换为常规整数列。
I want to support numbers from '1' to '10,000,000'.
我想支持从“1”到“10,000,000”的数字。
I've tried to use: to_number(fieldname, '999G999G999'), but it only works if the format matches the exact length of the string.
我尝试使用：to_number(fieldname, '999G999G999')，但它仅在格式与字符串的确切长度匹配时才有效。
Is there a way to do this that supports from '1' to '10,000,000'?
有没有办法做到这一点，支持从“1”到“10,000,000”？
回答by vol7ron
select replace(fieldname,',','')::numeric ;
To do it the way you originally attempted, which is not advised:
按照您最初尝试的方式进行操作，不建议这样做：
select to_number( fieldname,
regexp_replace( replace(fieldname,',','G') , '[0-9]' ,'9','g')
);
The inner replace changes commas to G. The outer replace changes numbers to 9. This does not factor in decimal or negative numbers.
内部替换将逗号更改为G. 外部替换将数字更改为9. 这不考虑小数或负数。
回答by Mike Christensen
You can just strip out the commas with the REPLACE()function:
您可以使用以下REPLACE()函数去除逗号：
CREATE TABLE Foo
(
Test NUMERIC
);
insert into Foo VALUES (REPLACE('1,234,567', ',', '')::numeric);
select * from Foo; -- Will show 1234567
回答by Daniel Castro
回答by kwky
There are things to take note:
When using function REPLACE("fieldName", ',', '')on a table, if there are VIEW using the TABLE, that function will not work properly. You must drop the view to use it.
有一点需要注意：
在表上使用函数REPLACE("fieldName", ',', '')时，如果有使用TABLE 的VIEW，该函数将无法正常工作。您必须删除视图才能使用它。
