oracle 如何使用子选择查询将逗号分隔的字符串值添加到'IN'子句中作为SQL中的项列表?

wpx232ag  于 2022-12-11  发布在  Oracle
关注(0)|答案(1)|浏览(192)

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.

wfauudbj

wfauudbj1#

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.

相关问题