SQL – Join with keys separated by pipe

TABLE_A

 CARGO - HELPERS
 250 - 125|126|5|...(AS MANY AS THEY ARE ASSIGNED ON THE PROGRAM)

TABLE_B

 ID - NAME
 5 - JAKE
 125 - JOSH
 126 - MEGAN
 9001 - VEGETA
 10 - SPANISH INQUISITION

How can I join table A and B to get all those who helped in the cargo?

Will I need to write a function?

I did not develop the program. I’m creating a dashboard and need that info.

Using ORACLE 11g DB

CONCLUSION — SOLVED

After a bit of research and help of friends I was able to get this SQL that worked in my case.

SELECT CARGO, HELPERS
FROM TABLE_A
INNER JOIN table_b B ON B.ID IN (SELECT regexp_substr(HELPERS,'[^|]+', 1, level) 
                                 FROM dual 
                                 CONNECT BY regexp_substr(HELPERS,'[^|]+', 1, level) IS NOT NULL) 

The use of regex to get all until the first |, then continue until can’t find another pipe. Not the correct explanation but can help you.

Answer

In a sense, this is easy:

...
from table_a a join table_b b 
               on '|' || a.helpers || '|' like '%|' || b.id || '|%'
...

But, as I said in a Comment under your question, the better approach would be to fix your data model (if it’s within your power). Your TABLE_A is not in First Normal Form. No wonder you had difficulty writing this (trivial) join; you will feel much more pain for less trivial tasks, not to mention slow execution, no ability to check the validity of “helpers”, etc.