Q:

SQL join table on partial string

I have two tables in a Postgres database:
Table A:
**Middle_name**
John
Joe
Fred
Jim Bob
Paul-John

Table B:
**Full_name**
Fred, Joe, Bobda
Jason, Fred, Anderson
Tom, John, Jefferson
Jackson, Jim Bob, Sager
Michael, Paul-John, Jensen

Sometimes the middle name is hyphenated or has a space between it. But there is never a comma in the middle name. If it is hyphenated or two middle names, the entries will still be the same in both Table A and Table B.
I want to join the tables on Middle_name and Full_name. The difficult part is that the join has to check only the values between the commas in Full_name. Otherwise it might match the first name accidentally.
I've been using the query below but I just realized that there is nothing stopping it from matching the middle name to a first name accidentally.
SELECT Full_name, Middle_name
FROM B
JOIN A
ON POSITION(Middle_name IN Full_name)>0

I'm wondering how I can refactor this query to match only the middle name (assuming they all appear in the same format).

A:

use  split_part('Fred, Joe, Bobda', ',', 2) which returns the middle name joe
SELECT Full_name, Middle_name
FROM B
JOIN A
ON split_part(B.Full_name, ',', 2)=A.Middle_name

demo for returning middle name

