upate statement using replace in PostgreSQL

Question

I have table having below records

Sno  A
-    --
1   spoo74399p 
2   spoo75399p

I want to update the above records by replacing oo (alphabet 'o') by zero

Required OUTPUT
----------------
Sno     A

1   sp0074399p 
2   sp0075399p

Duplicate of https://stackoverflow.com/questions/5060526/postgresql-replace-all-instances-of-a-string-within-text-field — newBee, Feb 11 '20 at 12:44
Does this answer your question? [postgresql - replace all instances of a string within text field](https://stackoverflow.com/questions/5060526/postgresql-replace-all-instances-of-a-string-within-text-field) — newBee, Feb 11 '20 at 12:44

score 1 · Answer 1 · answered Feb 11 '20 at 10:13

1

I want to update the above records by replacing oo (alphabet 'o') by zero

Is this what you are looking for?

update mytable set a = replace(a, 'oo', '00')

answered Feb 11 '20 at 10:13

GMB

score 1 · Accepted Answer · answered Feb 11 '20 at 10:15

1

I might use REGEXP_REPLACE here to be as specific as possible:

UPDATE yourTable
SET A = REGEXP_REPLACE(A, '^spoo', 'sp00');

This would only target the oo occurring near the beginning, after sp.

answered Feb 11 '20 at 10:15

Tim Biegeleisen

2 Answers2