PostgreSQL converts comma string to integer

Question

I want to convert a column of type "character differenting" that has comma-separated integers to a regular integer column.

I want to support numbers from "1" to "10,000,000."

I tried using: to_number (field name, "999G999G999"), but it only works if the format matches the exact length of the string.

Is there a way to do this that supports “1” to “10,000,000”?

Answer 1

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

---

To do this the way you originally tried, which is not recommended:

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

Internal replacement replaces commas with G An external replacement changes the numbers to 9 . This does not include decimal or negative numbers.

Answer 2

You can simply disable the comma with the REPLACE() function:

 CREATE TABLE Foo ( Test NUMERIC ); insert into Foo VALUES (REPLACE('1,234,567', ',', '')::numeric); select * from Foo; -- Will show 1234567 

Answer 3

You can replace the commas with an empty string, as suggested, or you can use to_number with the FM prefix , so the query will look like this: this:

 SELECT to_number(my_column, 'FM99G999G999') 

Answer 4

There are things to consider:

When using the REPLACE function ("fieldName", ',', '') in the table, if there is a VIEW using TABLE, this function will not work properly. You must opt ​​out of viewing in order to use it.