SQL拆分逗号分隔字符串为行用于IN子句报ORA-00932错误求解

现有逗号分隔格式字符串，示例值：

'one,two,three'

需要将字符串拆分为多行值，用于SQL语句的IN子句，拆分后的预期结果为逐行展示单个值：

one
two
three

最初尝试使用XMLTable实现拆分，单独执行拆分查询时可以正常返回结果，但将该子查询放入IN条件中时执行失败，测试语句如下：

SELECT 1 
  FROM dual 
 WHERE 'one' IN (SELECT column_value 
                   FROM XMLTable('"one","two","three"'));

执行后抛出错误：

ORA-00932: inconsistent datatypes: expected - got CHAR
00932. 00000 - "inconsistent datatypes: expected %s got %s"
要求提供不使用PL/SQL的纯SQL解决方案。

XMLTable默认返回的column_value字段是XMLType类型，而非字符串类型。单独执行查询时，客户端工具会自动做隐式类型转换，因此看起来返回正常；但放入IN子句做等值匹配时，Oracle无法自动完成XMLType和CHAR/VARCHAR2类型的隐式转换，因此抛出类型不匹配错误。

以下两种方案均为纯SQL实现，无需依赖PL/SQL对象。

方案1：修正XMLTable用法，显式指定返回列类型 #

在XMLTable语法中明确定义返回值的列名和类型，避免使用默认的XMLType类型返回值：

SELECT 1 
  FROM dual 
 WHERE 'one' IN (
        SELECT val
          FROM XMLTable(
                 '"one","two","three"'
                 COLUMNS val VARCHAR2(200) PATH '.'
               )
);

如果原始输入是不带引号的纯逗号分隔字符串（即最初提到的'one,two,three'格式），可以用通用写法，无需手动给每个值加双引号：

SELECT 1 
  FROM dual 
 WHERE 'one' IN (
        SELECT TRIM(COLUMN_VALUE)
          FROM XMLTable(
                 '<r><v>' || REPLACE('one,two,three', ',', '</v><v>') || '</v></r>'
               )
);

根据实际拆分后的值长度，调整VARCHAR2的长度参数即可。

方案2：正则搭配层级查询拆分，无XML类型依赖 #

如果不想使用XML相关语法，可以用正则拆分+层级查询的方式实现，完全不存在类型匹配问题：

SELECT 1
  FROM dual
 WHERE 'one' IN (
        SELECT REGEXP_SUBSTR('one,two,three', '[^,]+', 1, LEVEL)
          FROM dual
       CONNECT BY REGEXP_SUBSTR('one,two,three', '[^,]+', 1, LEVEL) IS NOT NULL
);

如果字符串中存在多余空格、连续逗号的场景，可以在外层套TRIM函数处理空格，加IS NOT NULL条件过滤空值即可。

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