PostgreSQL将带有逗号的字符串转换为整数

Question

我想把一个"character varying"类型的列中带有逗号的整数转换为普通整数列。

我希望支持从"1"到"10,000,000"的数字。

我尝试使用: to_number(fieldname, '999G999G999')，但只有当格式与字符串的长度完全匹配时才有效。

有没有一种方法可以支持从"1"到"10,000,000"的范围进行转换？

- Aaron Kreider

---

我使用这个函数来从任意字符串中解析整数。 - Oleg Mikhailov

Answer 1

select replace(fieldname,',','')::numeric ;

按照您最初尝试的方式来做，这并不被建议：

select to_number( fieldname,
                  regexp_replace( replace(fieldname,',','G') , '[0-9]' ,'9','g')
                );

内部的替换将逗号替换为G。外部的替换将数字替换为9。这不考虑小数或负数。

- vol7ron

---

替换逗号是聪明、简单，而且可能比使用to_number()函数快得多。谢谢。 - Aaron Kreider

有一种方法是使用 to_number 函数（如果您感兴趣的话）.. 请查看我的答案。 - Daniel Castro

@vol7ron 我有一个问题。当应用程序/网站与不同地区一起工作时，用户会根据其本地标准插入值。德国人使用点作为千位分隔符和逗号作为小数分隔符，而许多地区则反之。现在，我的问题是，PostgreSQL存储带有小数和千位分隔符的数字的格式是什么？ - Kamal Bharakhda

Answer 2

你可以使用REPLACE()函数去掉逗号:

CREATE TABLE Foo
(
  Test NUMERIC
);

insert into Foo VALUES (REPLACE('1,234,567', ',', '')::numeric);

select * from Foo; -- Will show 1234567

- Mike Christensen

Answer 3

您可以按照建议将逗号替换为空字符串，或者使用带有FM前缀的to_number函数，这样查询将如下所示：

SELECT to_number(my_column, 'FM99G999G999')

- Daniel Castro

---

select to_number('1,000,000,065','FM99G999G999');的结果为100000（与1,000,000,000和10,000,000相同）。 FM是填充模式；它只压制填充空格和尾随的0。 - vol7ron

@vol7ron 如果使用格式来接受更大的数字，例如 'FM999G999G999G999G999'，它仍然有效。 不过，看起来to_number有点小问题，因为它不能处理 '9G999G999G999'。 - Daniel Castro

在这里检查一下：http://sqlfiddle.com/#!12/557d7/2 ，它有足够的占位符，但会丢掉最后一位数字（奇数）。 - vol7ron

这是在9.6中的相同示例，似乎问题仍然存在（http://sqlfiddle.com/#!17/538b7/1）。我从未提交过错误报告。 - vol7ron

Answer 4

有一些需要注意的事项：

当在表上使用函数REPLACE("fieldName", ',', '')时，如果有视图使用该表，则该函数将无法正常工作。您必须删除视图才能使用它。

- kwky

---

这很重要，但应该作为其他答案的评论。单独来看，它不能独立回答问题，而是似乎在参考之前的答案。 - Brian H.