CODEWITHSUNDEEP
sqlpostgresql
Matt
Matt

Reputation: 1632

SQL join tables if one string appears in another string

Let's say if have two tables

Table A:

**Middle_name**
John
Joe
Fred

Table B:

**Full_name**
Billy, Joe-Bob
JasonFredAnderson
Tom John Jefferson

I want to join the tables on Middle_name and Full_name.

I've tried using partial string matching like below but that hasn't worked. For some reason it doesn't match full names with a comma before the middle name. Using postgres.

SELECT Full_name, Middle_name
FROM B
JOIN A
ON B.Full_name LIKE CONCAT('%', A.Middle_name, '%')

To reiterate, the above query would join Fred and John correctly, but not Joe, since Billy, Joe-Bob has a comma before Joe.

Upvotes: 0

Views: 555

Answers (1)

helloworld
helloworld

Reputation: 456

I believe you can do this using the POSITIION() function this way: 

SELECT Full_name, Middle_name
FROM B
JOIN A
ON POSITION(Middle_name IN Full_name)>0

Upvotes: 2

Related Questions