SQL: Parse Comma-delimited string and use as join

Question

How can I parsed a string comma-delimited string from a column in Oracle database and use each one as join to another table

example:

     Table 1                    Table 2

 ID      Name                  ID       Rate
 ---    ------                ----     ------
 1,2,3  FISRT                   1        90
                                2        80      
                                3        70

Would like to extract each ID from Table 1 to join in Table 2, something like

extract(tbl1.ID, i) = tbl2.ID

Thanks in advance.

You shouldn't be storing values like that in the first place. Did you consider fixing your datamodel? — , Sep 03 '13 at 06:59
possible duplicate of [Parse varchar2 to table (Oracle)](http://stackoverflow.com/questions/17582006/parse-varchar2-to-table-oracle) — Ben, Sep 03 '13 at 07:00
This is not exactly what you're asking, but might be of some help http://stackoverflow.com/questions/13580245/sending-an-array-of-values-to-oracle-procedure-to-use-in-where-in-clause — A.B.Cade, Sep 03 '13 at 07:00
possible duplicate of [Converting a delimited string (or column) to rows in Oracle using a pre-defined system function](http://stackoverflow.com/questions/5825356/converting-a-delimited-string-or-column-to-rows-in-oracle-using-a-pre-defined) — Klas Lindbäck, Sep 03 '13 at 07:03
I think my question is not duplicate of the questions you suggested since I need not only parsing the comma-delimited string, but I also need to find a way to use the parsed result in joins — Ianthe, Sep 03 '13 at 07:09
Once you've parsed it then of course you can use it in a join... — Ben, Sep 03 '13 at 07:19
@Ben It's not completly trivial, and quite inefficient to go from the function described in Klas's link to a join. And lanthe does not actually need to parse the string. It's easier to look for substrings. — Taemyr, Sep 03 '13 at 07:22
It is trivial @Taemyr; you get a column back and the results are shown in the answer. I never said it was efficient; everything to do with parsing delimited strings in a RDBMS is highly _inefficient_ when compared to a simple JOIN. — Ben, Sep 03 '13 at 07:26
@Ben As the answer notes, SYS.DBMS_DEBUG_VC2COLL, does not create a column. You get a table for each value you pass. So the join condition would be somethin like ...tbl2.ID in (Select column_value from table(SYS.DBMS_DEBUG_VC2COLL(tbl1.ID)) — Taemyr, Sep 03 '13 at 09:10
One you've selected from the table @Taemyr you have a column; using a with clause as per the answer makes this fairly obvious how to use it. I would avoid an IN statement. — Ben, Sep 03 '13 at 09:20
You can use a JOIN @Taemyr; http://www.sqlfiddle.com/#!4/22b80/3 — Ben, Sep 03 '13 at 10:17
@Ben Yes, you can join against the elements in the column you create. But when the data comes from a table you need to create that column for each entry in the original table. So your way of formating the query at least needs a severe rewrite. - And I am still not sure how that should be done. — Taemyr, Sep 03 '13 at 14:39

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

Based on this answer you can do something like this

select *
from table1 t1 join table2 t2 on ','||t1.id||',' like '%,'||t2.id||',%'

here is a sqlfiddle

edited May 23 '17 at 11:53

Community

1
1

answered Sep 03 '13 at 07:13

A.B.Cade

16,735
1
37
53

score 0 · Answer 2 · edited May 23 '17 at 11:53

The typical way is the use a hierarchical query and REGEXP_SUBSTR. If you have one row then this is okayish; if you have multiple then you've got problems.

The following will parse the comma-delimited string.

 select regexp_substr(:txt, '[^,]+', 1, level)
   from dual
connect by regexp_substr(:txt, '[^,]+', 1, level) is not null

However, if you're doing more than one at once you will need to add a DISTINCT see duplicating entries in listagg function.

You can then use this in your JOIN.

with the_rows as (
 select distinct regexp_substr(col, '[^,]+', 1, level) as id
   from table1
connect by regexp_substr(col, '[^,]+', 1, level) is not null
        )
 select *
   from table2 a
   join the_rows b
     on a.id = b.id

This is a horrible way to do it; you're using hierarchical queries and then a sort unique for the DISTINCT. It is about as far from efficient as you can get. You need to normalise your table.

Thanks Ben for the answer, the performance is really so bad... :( is there any other way to do it? — Ianthe, Sep 03 '13 at 07:19
A.B. Cade's answer will also work but you'll be full-scanning table2 for every query. You have to normalise your database @Ianthe. — Ben, Sep 03 '13 at 07:21

score 0 · Answer 3 · answered Sep 03 '13 at 08:34

Thank you all for the ideas and comments, maybe I do not really need to join the parsed strings. I found somnething like "REGEXP_LIKE", below is what I tried, will this the same as I joined the 2 tables?

SELECT t2.id, t2.rate
  FROM table1 t1, table2 t2
    WHERE (REGEXP_LIKE(t1.id,
                ',' || t2.id || ',|^' || t2.id || ',|,' || t2.id ||
                '$|^' || t2.id || '$'))

SQL: Parse Comma-delimited string and use as join

3 Answers3

Linked

Related