Oracle sql Join tables based on one table containing the word in the field of another table

Question

I'm trying to join two tables together as follows.

Table A        Table B
Field1         Field1
GO             GO
FOREGO
OK GO

I only want to join where the field1 in table A has the exact word as a word in table B, but is not part of another word. So GO and OK GO would join successfully, FOREGO would not be allowed to join.

I have a hunch that i'd use reg_exp to accomplish the join, but I can't figure out how to implement.

Answer 1

I have a hunch that i'd use reg_exp to accomplish the join, but I can't figure out how to implement.

You could use REGEXP_LIKE:

SELECT DISTINCT A.*
FROM TableA A
JOIN TAbleB B
 ON regexp_like(A.Field1, '( |^)('|| B.Field1|| ')( |$)');

DBFiddle Demo

Answer 2

Assuming that words are surrounded by spaces, you could do:

select a.*
from a join
     b
     on ' ' || a.field1 || ' ' like '% ' || b.field1 || ' %';

Neither this method nor the version using regexp_substr() is going to have very good performance. I would suggest that you parse the words out of table b into another table, and use a simple equijoin.