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');