oracle sql query

Question

I have a table A with column code which contains values AB,BC

-----
code
-----
'AB','BC'

Table B has following row

------------------------------
AB   4  2  1   
------------------------------
-------------------------------
BC   1  2  3
--------------------------------

For AB there is an row entry in another table B and similarly for BC

i want to use IN clause for the column code

select regexp_substr(replace (a.code,'''',''), '[^,]+', 1, level) from  A a , C b 
WHERE a.id ='SC_1' AND a.id= b.id AND a.name = 'CODE'
connect by level <= length(regexp_replace(replace (a.code,'''',''), '[^,]*')) + 1

when i execute above sql there is no result , i think the IN clause require row rather comma separated.kindly let me know how to work out on this.

Answer 1

You will need to split your code string across several rows. Try:

select * from B
where B.code in (
    select regexp_substr(code, '[^,]+', 1, level) from A
    connect by level <= length(regexp_replace(code, '[^,]*')) + 1)