Replace character with another character in postgresql

Question

I have a procedure in Postgres which is doing just fine. It is storing data in a database table which you can use them later. One column in the database table stores names of customers. Some of the names have special chars such as apostrophe '. How can I replace this character with an empty string in Postgres?

UPDATE 
   booking
SET 
   name= REPLACE(name,"'",'')
WHERE 
   booking_id = 1;

That sounds as if you are using string literals incorrectly. Please Edit your question and add the code of your function Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Nov 11, 2017 at 8:03

laroja · Accepted Answer · 2017-11-23 03:29:39Z

5

Here is what i got the solution I used the replace function from postgres function list in my query and worked like a charm.The REPLACE function takes in three params,the first is the table column you want to replace,the second param is the pattern match you want to replace and the third param is the character to replace the ones you don't want

    SELECT replace([table_column],'''','') as name from table_name WHERE [condition]

answered Nov 23, 2017 at 3:29

laroja

1032 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2017-11-11 07:25:38Z

2

You could use REPLACE:

UPDATE table_name
SET name = REPLACE(name, '''', '')
WHERE name LIKE '%''%';

Rextester Demo

answered Nov 11, 2017 at 7:25

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

5 Comments

laroja Over a year ago

I have tried to use that but am getting error something like ' column "'" does not exist' seems like it is only for table columns,right??

laroja Over a year ago

To me i want to replace say the name O'Neil to ONeil not the column name

zerkms Over a year ago

@laroja show the exact query you have run. The query from this answer cannot trigger the error you provided.

laroja Over a year ago

@zerkms this is the query UPDATE customer SET name= REPLACE(name,"'",'') WHERE booking_id =1;

zerkms Over a year ago

@laroja if you check the answer again you'd spot there are no double quotes " there, all those are single quotes '

Collectives™ on Stack Overflow

Replace character with another character in postgresql

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related