SQL join table on partial string

Question

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).

I don't have a middle name. Just sayin ... – Erwin Brandstetter Mar 20 '19 at 19:11 — Erwin Brandstetter, Mar 20 '19 at 19:11

score 2 · Accepted Answer · answered Mar 20 '19 at 18:56

2

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

answered Mar 20 '19 at 18:56

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 2 · answered Mar 20 '19 at 19:12

If there is always exactly one space after the comma, and everybody has a middle name like your sample data suggests, the space can just be part of the delimiter in split_part():

SELECT full_name, middle_name
FROM   A
JOIN   B ON split_part(B.full_name, ', ', 2) = A.middle_name;

Split comma separated column data into additional columns

SQL join table on partial string

2 Answers2