By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE t1 ( id INT, employee TEXT);
INSERT INTO t1 VALUES
(1, 'Mr. John Cole Johnson'),
(2, 'Mr. Paul George Mckenzie'),
(3, 'Mr. George Mick McDoughal'),
(4, 'Ms. Emily Suzan Flemming'),
(5, 'Mr. Alan Bourdillion Traherne');
5 rows affected
CREATE TABLE t2 (id INT, first_name TEXT);
INSERT INTO t2 VALUES
(1, 'Emily'),
(2, 'John'),
(3, 'George'),
(4, 'Suzan'),
(5, 'Paul'),
(6, 'Alan'),
(7, 'Mary'),
(8, 'Mick'),
(9, 'Bourdillion'),
(10, 'Jim'),
(11, 'Cole')
11 rows affected
WITH RECURSIVE cte AS (
SELECT employee, 1 id
FROM t1
UNION ALL
SELECT REPLACE(employee, first_name, ''), id+1
FROM cte
JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte
WHERE id > ( SELECT MAX(id)
FROM t2 )
| employee |
|---|
| Mr. son |
| Mr. Mckenzie |
| Mr. McDoughal |
| Ms. Flemming |
| Mr. Traherne |
WITH RECURSIVE cte AS (
SELECT employee || ' ' employee, 1 id
FROM t1
UNION ALL
SELECT REPLACE(employee, ' ' || first_name || ' ', ' '), id+1
FROM cte
JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte
WHERE id > ( SELECT MAX(id)
FROM t2 )
| employee |
|---|
| Mr. Johnson |
| Mr. Mckenzie |
| Mr. McDoughal |
| Ms. Flemming |
| Mr. Traherne |