How to add comma-separated string value to an 'IN' clause as an item list in SQL using sub select query?

Question

I have columnA of data type VARCHAR2(4000 BYTE) in an Oracle database tableA. columnA has comma-separated values as shown below (shows one record of the columnA for id=1).

I need to insert the record as separate list of values into an IN clause using a sub select query.

Below is what I tried.

... IN (SELECT tableA.columnA FROM tableA WHERE tableA.id = '1')

Expected:

... IN (2222222226,2222222224,2222222227)

But I don't get the expected result. Please suggest a way for this.

Answer 1

You can also try:

FROM other_table
WHERE columnA IN (
    SELECT regexp_substr(columnA, '[^,]+', 1, LEVEL)
    FROM tableA
    CONNECT BY regexp_substr(columnA, '[^,]+', 1, LEVEL) IS NOT NULL
)

Here in SQLFiddle.