Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE strings(id serial PRIMARY KEY, string text);

INSERT INTO strings(string) VALUES
  ('John Doe lives in Emerald Street')
, ('John Doe lives in Emerald Street as john DOE Emerald');

CREATE TABLE

INSERT 0 2

SELECT *, rtrim(regexp_replace(string, '(John|Doe|Emerald) ?', '', 'g')) FROM strings;

id
string
rtrim

1
John Doe lives in Emerald Street
lives in Street

2
John Doe lives in Emerald Street as john DOE Emerald
lives in Street as john DOE

SELECT 2

SELECT *, rtrim(regexp_replace(string, '(' || replace('John Doe Emerald', ' ', '|') || ') ?', '', 'g')) FROM strings;

id
string
rtrim

1
John Doe lives in Emerald Street
lives in Street

2
John Doe lives in Emerald Street as john DOE Emerald
lives in Street as john DOE

SELECT 2

-- minimal form
SELECT s.id, string_agg(word, ' ') AS string2
FROM   strings s, unnest(string_to_array(s.string, ' ')) word
WHERE  word <> ALL (string_to_array('John Doe Emerald', ' '))
GROUP  BY 1
ORDER  BY 1;

id
string2

1
lives in Street

2
lives in Street as john DOE

SELECT 2

-- guarantee order
SELECT s.id, string_agg(word, ' ' ORDER BY ord) AS string2
FROM   strings s, unnest(string_to_array(s.string, ' ')) WITH ORDINALITY AS t(word, ord)
WHERE  t.word <> ALL (string_to_array('John Doe Emerald', ' '))
GROUP  BY 1
ORDER  BY 1;

id
string2

1
lives in Street

2
lives in Street as john DOE

SELECT 2

-- typially faster
SELECT sub.id, string_agg(sub.word, ' ') AS string2
FROM  (
   SELECT s.id, t.word
   FROM   strings s, unnest(string_to_array(s.string, ' ')) WITH ORDINALITY AS t(word, ord)
   WHERE  t.word <> ALL (string_to_array('John Doe Emerald', ' '))
   ORDER  BY s.id, t.ord
   ) sub
GROUP  BY 1
ORDER  BY 1;

id
string2

1
lives in Street

2
lives in Street as john DOE

SELECT 2

-- often simpler to integrate with LATERAL subquery:
SELECT s.id, sub.string2
FROM   strings s
CROSS  JOIN LATERAL (
   SELECT string_agg(t.word, ' ' ORDER BY t.ord) AS string2
   FROM   unnest(string_to_array(s.string, ' ')) WITH ORDINALITY AS t(word, ord)
   WHERE  t.word <> ALL (string_to_array('John Doe Emerald', ' '))   
   ) sub
ORDER  BY 1;

id
string2

1
lives in Street

2
lives in Street as john DOE

SELECT 2
id	string	rtrim
1	John Doe lives in Emerald Street	lives in Street
2	John Doe lives in Emerald Street as john DOE Emerald	lives in Street as john DOE