Home > Mobile >  How do I remove all commas from a text column in PostgreSQL
How do I remove all commas from a text column in PostgreSQL

Time:05-26

I have a table called 'master' such as:

item cost
apple 4.47
car 7,232.64
bike 499.99

Currently item and cost are both TEXT data types. I want to convert cost to NUMERIC but get an error because of the comma in the car price.

How can I remove ALL commas from the cost column?

dbname=# ALTER TABLE master ALTER COLUMN cost TYPE numeric USING cost::numeric;  

ERROR: invalid input syntax for type numeric: "7,232.64"

CodePudding user response:

Use regexp_replace:

UPDATE master SET cost = regexp_replace(cost, ',', '', 'g');
  • Related