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? — user330315
– user330315, Commented Sep 3, 2013 at 6:59
This is not exactly what you're asking, but might be of some help stackoverflow.com/questions/13580245/… — A.B.Cade
– A.B.Cade, Commented Sep 3, 2013 at 7:00
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
– Ben, Commented Sep 3, 2013 at 7:26

Community · Accepted Answer · 2017-05-23 11:53:37Z

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, 2017 at 11:53

CommunityBot

11 silver badge

answered Sep 3, 2013 at 7:13

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:53:37Z

0

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.

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Sep 3, 2013 at 7:02

Ben

53.1k36 gold badges133 silver badges156 bronze badges

2 Comments

Ianthe Over a year ago

Thanks Ben for the answer, the performance is really so bad... :( is there any other way to do it?

Ben Over a year ago

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.

Ianthe · Accepted Answer · 2013-09-03 08:34:11Z

0

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 || '$'))

answered Sep 3, 2013 at 8:34

Ianthe

5,73921 gold badges61 silver badges75 bronze badges

Collectives™ on Stack Overflow

SQL: Parse Comma-delimited string and use as join

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related