Snowflake - Joining two tables where one table's IDs are delimited by a semicolon-CodePudding

I am working within Snowflake and some of the IDs in a given table and column are delimited by semicolons. Despite this delimiter the tables should still be joined. Any attempt to join the table is usually met with an error of some sort.

Below I have an example of what I have attempted to do.

       table_A                       table_B
 ---------- ----------        ---------- ----------  
| some_id  |  F_Name  |      | some_id  |  L_Name  |
 ---------- ----------        ---------- ---------- 
|   12345  |   John   |      |12345;4321|    Doe   |
 ---------- ----------        ---------- ---------- 

Attempted SQL Statement

select table_A.some_id, tableF_name, L_Name
from table_A
left join table_B on table_A.some_id like '%'||table_B.some_id||'%'

The source for this particular problem has show up here but it doesn't seem to work. It may just not be possible to do joins in this particular way.

CodePudding user response：

If you use a CTE to flatten the second table and then filter out duplicates

WITH expand AS (
    SELECT 
       f.seq as seq,
       f.index as index
       f.value as join_token
       t.l_name
    FROM table_b t,
      lateral split_to_table(t.some_id, ';') f
)
SELECT 
  a.some_id, 
  a.f_name, 
  f.l_name
FROM table_a AS a
LEFT JOIN expand AS f
    ON a.some_id = f.join_token
QUALIFY row_number() over (partition by f.seq ORDER BY f.index) == 1