Postgresql replace all occurrences of string+

Question

I have this string:

this is the abcd xxx
string I want to abcd yyy
replace in my text abcd zzz

Now I want to replace abcd and anything after it with blank.

I want this result:

this is the
string I want to 
replace in my text 

I tried:

select regexp_replace(str, 'abcd.*','','gi')

But it just removed everything after the first match. Also other combos without luck.

What am I missing?

Thanks!

Answer 1

Use the flag n (newline-sensitive matching) in regexp_replace():

with my_table(str) as (
values(
'this is the abcd xxx
string I want to abcd yyy
replace in my text abcd zzz')
)

select regexp_replace(str, 'abcd.*','','gin')
from my_table

   regexp_replace    
-----------------
 this is the        +
 string I want to   +
 replace in my text 
(1 row)