I have a table with a column containing names like this:
id, employee 1, Mr. John Cole Thornton 2, Mr. Paul George Mckenzie 3, Mr. George Mick McDoughal 4, Ms. Emily Suzan Flemming 5, Mr. Alan Bourdillion Traherne
I have a second table with a list of first names, like this
id, first_name 1, Emily 2, John 3, George 4, Suzan 5, Paul 6, Alan 7, Mary 8, Mick 9, Bourdillion 10, Jim 11, Cole
And I want to remove the first names in the first table, in order to obtain this:
id, employee 1, Mr. Thornton 2, Mr. Mckenzie 3, Mr. McDoughal 4, Ms. Flemming 5, Mr. Traherne
No matter how many first names, I would like to remove them all without calling several times my first_names table, and I wonder if this would be possible without using a loop in a function.
I have tried a query like this :
WITH RECURSIVE name AS ( SELECT REPLACE(t1.employee, t2.first_name, '') sec_name FROM t1, t2 WHERE position(t2.first_name in t1.employee) > 0 ) SELECT sec_name FROM name ;
But I get as many output as there are first names in the input, like :
Id, sec_name 1, John Thornton 1, Cole Thornton 2, Paul Mckenzie 2, George Mckenzie ...
My Postgres version is 9.6 .
Any help will be much appreciated !