Rose Rose - 4 months ago 25
SQL Question

oracle SQL — Replace comma separated string with another set of values

I have the following table:

B_ID I_ID R_ID
W00001 1234 1235,1237
B00001 1235 1236,1235
T00001 1236 1235,1235,1235
X00001 1237 1234,1236,1238
M00001 1238 1238


I need output like below using sql

B_ID I_ID R_ID
W00001 1234 B00001|X00001
B00001 1235 T00001|B00001
T00001 1236 B00001
X00001 1237 W00001|T00001|M00001
M00001 1238 M00001

1. R_ID should match its value with I_ID and pick the corresponding B_ID.
2. Comma's present in R_ID column should replaced with '|' delimiter.


Example : 1st row R_ID has values 1235,1237. 1235 and 1237 is present in I_ID so their corresponding B_ID is picked i.e B00001,X00001 and expected output is B00001|X00001

Answer

In the solution below, I use a standard technique to split each comma-separated string into components (tokens) in factored subquery prep. Then I join back to the original table to replace each token (which is an i_id) with the corresponding b_id, and then put the tokens back together into pipe-separated strings with listagg().

Note: This solution assumes that each r_id has fewer than 100 tokens (see the "magic number" 100 in the definition of idx). If it is known that each r_id will have no more than 9 tokens, then 100 can be changed to 10 (resulting in faster processing). If NO upper bound is known beforehand, you can change 100 to some ridiculously large number; 4000 will do if r_id is anything but a CLOB, as VARCHAR2 and such have a limit of 4000 characters.

Thanks to MT0 for reminding me to add this note.

with test_data ( b_id, i_id, r_id ) as (
       select 'W00001', 1234, '1235,1237'      from dual union all
       select 'B00001', 1235, '1236,1235'      from dual union all
       select 'T00001', 1236, '1235,1235,1235' from dual union all
       select 'X00001', 1237, '1234,1236,1238' from dual union all
       select 'M00001', 1238, '1238'           from dual
     ),
     idx ( n ) as (
       select level from dual connect by level < 100
     ),
     prep ( b_id, i_id, n, token ) as (
       select t.b_id, t.i_id, i.n,
                regexp_substr(t.r_id, '([^,]+)', 1, i.n, null, 1)
       from   test_data t join idx i
                          on i.n <= regexp_count(t.r_id, ',') + 1
     )
select p.b_id, p.i_id, 
       listagg(t.b_id, '|') within group (order by p.n) as r_id
from   prep p join test_data t
              on p.token = t.i_id
group by p.b_id, p.i_id
order by p.i_id;



B_ID         I_ID R_ID
------ ---------- ------------------------------
W00001       1234 B00001|X00001
B00001       1235 T00001|B00001
T00001       1236 B00001|B00001|B00001
X00001       1237 W00001|T00001|M00001
M00001       1238 M00001