如何将分隔字符串转换为PL/SQL表以进行连接?

我能想到的最简单的解决方案(尽管它可能不是最有效的)就是使用一个简单的 INSTR

WITH
    t_data
    AS
        (    SELECT 'id' || ROWNUM        AS id,
                    'value' || ROWNUM     AS VALUE,
                    ROWNUM                AS index_num,
                    SYSDATE - ROWNUM      AS valid_from,
                    'A'                   AS entry_state
               FROM DUAL
         CONNECT BY LEVEL <= 10)
SELECT *
  FROM t_data
 WHERE INSTR ('id1:value1,id3:value3', id || ':' || VALUE) > 0;

如果要拆分搜索字符串,可以尝试以下查询

WITH
    t_data
    AS
        (    SELECT 'id' || ROWNUM        AS id,
                    'value' || ROWNUM     AS VALUE,
                    ROWNUM                AS index_num,
                    SYSDATE - ROWNUM      AS valid_from,
                    'A'                   AS entry_state
               FROM DUAL
         CONNECT BY LEVEL <= 10),
  split_string AS (SELECT 'id1:value1,id3:value3' AS str FROM DUAL),
  split_data as (
    SELECT substr(regexp_substr(str, '[^,]+', 1, LEVEL),1,instr(regexp_substr(str, '[^,]+', 1, LEVEL), ':') - 1) as id,
           substr(regexp_substr(str, '[^,]+', 1, LEVEL),instr(regexp_substr(str, '[^,]+', 1, LEVEL), ':') + 1) as value
      FROM split_string
CONNECT BY INSTR (str, ',', 1, LEVEL - 1) > 0)
SELECT t.*
  FROM t_data t
  join split_data s
 on( t.id = s.id and t.value = s.value);

LIKE 详情如下:

SELECT *
  FROM T_DATA 
 WHERE ',' || YOUR_STRING || ',' LIKE '%,' || ID || ':' || VALUE || ',%'