PostgreSQL convert a string with commas into an integer

Question

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'.

I've tried to use: to_number(fieldname, '999G999G999'), but it only works if the format matches the exact length of the string.

Is there a way to do this that supports from '1' to '10,000,000'?

Answer 1

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.