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.

A comma separated string is not the same as separate values in in clause. You're essentially doing ... IN ('2222222226,2222222224,2222222227') — James Z
– James Z, Commented Dec 6, 2022 at 16:23
You will need to parse the string and split it into multiple values - which you can do in your subquery. There are lots of questions about how to tokenize a comma-separated string. — Alex Poole
– Alex Poole, Commented Dec 6, 2022 at 16:25

mucio · Accepted Answer · 2022-12-06 17:30:13Z

3

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.

edited Dec 6, 2022 at 17:30

answered Dec 6, 2022 at 16:28

mucio

7,1271 gold badge23 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related