update statement with 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 empty after sp afte Required OUTPUT ---------------- Sno A

1   sp74399p 
2   sp75399p

Unrelated to your problem, but: Postgres 9.2 is no longer supported you should plan an upgrade as soon as possible. — user330315
– user330315, Commented Feb 14, 2020 at 7:55

Marian · Accepted Answer · 2020-04-29 22:51:00Z

3

UPDATE my_table
SET A = REPLACE(A, 'spoo', 'sp');

This would update all the records.

To clarify a bit, this would find any instances of "spoo" and replace them with "sp". The end result is that any "oo" right after "sp" would be deleted.

edited Apr 29, 2020 at 22:51

answered Feb 14, 2020 at 6:17

Marian

4,0992 gold badges29 silver badges38 bronze badges

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

4 Comments

Ram Over a year ago

i think sp should not come.'oo' replaced by empty.so Replace(A,'oo','').is it correct?

Marian Over a year ago

@Ram You can also replace 'oo' with ''. It's just a rule of thumb to replace the longer sequence. This way if you find 'oo' in a different part of the string, it will not be replaced.

Ram Over a year ago

if i put sp in replace command it replace the 'oo' by 'sp'.but i want to replace 'oo' with empty.so pls give the update command for this.

Marian Over a year ago

If you want to replace oo with empty after sp, use the command in my answer. If you want to replace any oo with empty, use REPLACE(A, 'oo', '').

Collectives™ on Stack Overflow

update statement with replace in postgresql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related