11

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

Aaron Kreider
  • 1,705
  • 5
  • 22
  • 44
  • I use [this function](http://stackoverflow.com/questions/2082686/how-do-i-cast-a-string-to-integer-and-have-0-in-case-of-error-in-the-cast-with-p/36859100#36859100) for parsing `integer` from an arbitrary string. – Oleg Mikhailov Apr 26 '16 at 08:24

4 Answers4

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

vol7ron
  • 40,809
  • 21
  • 119
  • 172
  • Replacing the comma is smart, easy, and probably a lot faster than using to_number(). Thanks. – Aaron Kreider Sep 18 '13 at 22:12
  • There is a way using to_number (in case you're interested).. Please look my answer – Daniel Castro Sep 18 '13 at 22:13
  • @vol7ron I have a query. When an application/website works with different regions, users try to insert values based on their local standards. Germans use dots as thousand separators and commas as decimal separators while many regions use them inversely. Now, my question is, what is the format of PostgreSQL to store numbers with a decimal's and thousand's separators? – Kamal Bharakhda May 10 '22 at 20:23
1

You can just strip out the commas with the REPLACE() function:

CREATE TABLE Foo
(
  Test NUMERIC
);

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

select * from Foo; -- Will show 1234567
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
0

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

SELECT to_number(my_column, 'FM99G999G999')
Daniel Castro
  • 1,290
  • 2
  • 11
  • 22
  • `select to_number('1,000,000,065','FM99G999G999');` results in `100000` (and so does `1,000,000,000` and `10,000,000`). `FM` is a fill mode; which only suppresses padding blanks and trailing `0`s – vol7ron Sep 19 '13 at 14:14
  • @vol7ron It still works if you use a format to admit larger numbers, e.g `'FM999G999G999G999G999'`. Still, it looks like to_number is a little buggy, because it does not work with '9G999G999G999' – Daniel Castro Sep 19 '13 at 17:39
  • yeah check [here](http://sqlfiddle.com/#!12/557d7/2) , which has enough placeholders but drops the last digit (odd) – vol7ron Sep 19 '13 at 19:49
  • Here's the same example in 9.6, seems like the issue still exists (http://sqlfiddle.com/#!17/538b7/1) I never did submit a bug report – vol7ron Jul 06 '18 at 17:17
-1

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.

kwky
  • 389
  • 3
  • 9
  • This is important, but it should be a comment to one of the other answers. By itself, this does not answer the question independently but instead seems to reference previous answers. – Brian H. Nov 28 '18 at 17:38