Oracle SQL中如何对逗号分隔列表实现IN操作符查询效果

针对传入逗号分隔值字符串（如'1,2,3'）、无法直接拼接为IN()列表的场景，Oracle可通过以下几种方式实现匹配查询：

方案1：11g及以上版本原生正则匹配（无需自定义函数） #

直接在WHERE条件中用正则判断目标值是否存在于逗号分隔串中，提前处理字符串内的空格、补充分隔符边界避免匹配异常：

-- 示例：匹配t_user表id存在于传入逗号串中的记录
SELECT * FROM t_user 
WHERE REGEXP_LIKE(
  ',' || REPLACE('1, 2, 3', ' ', '') || ',',
  ',(' || id || '),'
);

如果需要实现类似FIND_IN_SET返回匹配位置的效果，可以使用REGEXP_INSTR函数，返回值为0时代表不存在匹配：

SELECT REGEXP_INSTR(','||REPLACE('1, 2, 3',' ','')||',', ',2,') AS match_pos FROM dual;

方案2：拆分字符串为结果集做IN/JOIN匹配 #

适合需要多值关联查询的场景，将逗号分隔串拆分为多行值后再做匹配，性能比全表正则匹配更稳定：
11g及以上版本可以用CONNECT BY层级查询实现拆分：

SELECT * FROM t_user 
WHERE id IN (
  SELECT TRIM(REGEXP_SUBSTR('1, 2, 3', '[^,]+', 1, LEVEL)) AS split_val
  FROM dual
  CONNECT BY LEVEL <= REGEXP_COUNT('1, 2, 3', ',') + 1
);

12c及以上版本可以用JSON_TABLE实现更高效的拆分：

SELECT * FROM t_user 
WHERE id IN (
  SELECT TRIM(VALUE) FROM JSON_TABLE(
    REPLACE('["'||REPLACE('1, 2, 3', ',', '","')||'"]', ' ', ''),
    '$[*]' COLUMNS(VALUE VARCHAR2(100) PATH '$')
  )
);

方案3：自定义FIND_IN_SET函数（高复用性） #

如果这类查询场景使用频率高，可以直接创建与MySQL用法完全一致的自定义函数，降低重复写逻辑的成本：

CREATE OR REPLACE FUNCTION FIND_IN_SET(p_search VARCHAR2, p_str_list VARCHAR2, p_sep VARCHAR2 DEFAULT ',')
RETURN NUMBER IS
  l_result NUMBER;
BEGIN
  SELECT pos INTO l_result FROM (
    SELECT 
      TRIM(REGEXP_SUBSTR(p_str_list, '[^'||p_sep||']+', 1, LEVEL)) AS val,
      LEVEL AS pos
    FROM dual
    CONNECT BY LEVEL <= REGEXP_COUNT(p_str_list, p_sep) + 1
  ) WHERE val = p_search;
  RETURN NVL(l_result, 0);
END;
/

函数创建完成后，调用逻辑和MySQL完全一致：

-- 匹配返回位置大于0即代表值存在于列表中
SELECT * FROM t_user WHERE FIND_IN_SET(id, '1, 2, 3') > 0;

注意：如果匹配的字段值本身包含逗号，需要调整分隔符匹配规则，避免出现误匹配。

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