PL/SQL中从逗号分隔参数提取匹配模式及LIKE替代方案咨询

一、从参数lv_ip中提取匹配模式 #

参数格式为'模式1|标识1,模式2|标识2'，核心是提取每个逗号分隔项里|之前的部分作为LIKE匹配模式，以下是两种常见实现方式：

1. 用正则表达式拆分参数 #

借助REGEXP_SUBSTR和层级查询拆分参数，生成临时模式列表后关联原表查询：

WITH pattern_list AS (
    SELECT 
        REGEXP_SUBSTR(lv_ip, '[^,]+', 1, LEVEL) AS full_item,
        REGEXP_SUBSTR(REGEXP_SUBSTR(lv_ip, '[^,]+', 1, LEVEL), '[^|]+', 1, 1) AS match_pattern
    FROM DUAL
    CONNECT BY LEVEL <= REGEXP_COUNT(lv_ip, ',') + 1
)
SELECT t.id, t.date, t.program, t.program_start_date
FROM table_1 t
JOIN pattern_list pl ON t.program LIKE pl.match_pattern;

2. 自定义字符串拆分函数 #

如果需要复用拆分逻辑，可编写返回表类型的自定义函数，直接关联查询：

-- 假设split_param函数返回包含match_pattern字段的表
SELECT t.id, t.date, t.program, t.program_start_date
FROM table_1 t
JOIN TABLE(split_param(lv_ip)) pl ON t.program LIKE pl.match_pattern;

二、LIKE运算符的替代方案 #

1. 正则表达式匹配（REGEXP_LIKE） #

将提取的模式转成正则规则，用REGEXP_LIKE实现更灵活的匹配：

WITH pattern_list AS (
    SELECT REGEXP_SUBSTR(lv_ip, '[^|]+', 1, 1) AS match_pattern
    FROM DUAL
    CONNECT BY LEVEL <= REGEXP_COUNT(lv_ip, ',') + 1
),
regex_pattern AS (
    SELECT LISTAGG(REPLACE(match_pattern, '%', '.*'), '|') WITHIN GROUP (ORDER BY NULL) AS regex_str
    FROM pattern_list
)
SELECT id, date, program, program_start_date
FROM table_1, regex_pattern
WHERE REGEXP_LIKE(program, regex_str);

2. 前缀匹配用SUBSTR #

如果所有模式都是前缀匹配（如MNS-GC%即开头为MNS-GC），用SUBSTR替代LIKE，配合前缀索引可提升性能：

WITH pattern_list AS (
    SELECT 
        REGEXP_SUBSTR(lv_ip, '[^|]+', 1, 1) AS match_pattern,
        LENGTH(REGEXP_SUBSTR(lv_ip, '[^|]+', 1, 1)) - 1 AS prefix_len -- 去除%的长度
    FROM DUAL
    CONNECT BY LEVEL <= REGEXP_COUNT(lv_ip, ',') + 1
)
SELECT t.id, t.date, t.program, t.program_start_date
FROM table_1 t
JOIN pattern_list pl ON SUBSTR(t.program, 1, pl.prefix_len) = SUBSTR(pl.match_pattern, 1, pl.prefix_len);

3. 全文索引（大数据量场景） #

若表数据量庞大且频繁进行模糊匹配，可给program字段创建全文索引，使用全文检索函数（如Oracle的CONTAINS）查询，性能远优于LIKE。

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