Oracle SQL如何去除内层查询输出单引号 实现IN子句正确匹配

你现有写法失效的核心原因是：demo_temp表中codes字段存储的是包含单引号的逗号拼接字符串（值为'201,601'），替换单引号后得到的仍然是201,601这个整体字符串，IN运算符会将它作为单个完整值和demo表的codes字段匹配，自然找不到对应的单值201、601，所以返回0行。

方案1：REGEXP_SUBSTR + 层级查询拆分（兼容所有Oracle版本） #

通过正则函数按逗号拆分拼接字符串，生成独立值的结果集再做IN匹配：

SELECT * 
FROM demo 
WHERE codes IN (
    SELECT REGEXP_SUBSTR(REPLACE(codes, '''', ''), '[^,]+', 1, LEVEL) 
    FROM demo_temp
    CONNECT BY REGEXP_SUBSTR(REPLACE(codes, '''', ''), '[^,]+', 1, LEVEL) IS NOT NULL
);

如果demo_temp存在多行数据，为了避免层级查询生成冗余数据，可以调整为：

SELECT * 
FROM demo 
WHERE codes IN (
    SELECT DISTINCT REGEXP_SUBSTR(REPLACE(codes, '''', ''), '[^,]+', 1, LEVEL) 
    FROM demo_temp
    CONNECT BY REGEXP_SUBSTR(REPLACE(codes, '''', ''), '[^,]+', 1, LEVEL) IS NOT NULL
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR ROWID = ROWID
);

方案2：XMLTABLE拆分（Oracle 12c及以上版本推荐） #

写法更简洁，性能更优：

SELECT *
FROM demo
WHERE codes IN (
    SELECT COLUMN_VALUE
    FROM demo_temp t,
         XMLTABLE(REPLACE(t.codes, '''', ''))
);

两种方案都可以将拼接的字符串拆分为201、601两个独立值的结果集，和demo表的codes匹配后即可返回你预期的15行数据。

内容的提问来源于stack exchange，提问作者Mudit B