Oracle SQL中如何避免使用LIKE运算符查询多值分隔字段

数据表信息 #

当前使用LIKE的查询方式 #

SELECT *
FROM   company_details
WHERE  company LIKE '%892%' -- Company为参数

SELECT *
FROM   company_details
WHERE  company LIKE '%682%' -- Company为参数

替代LIKE的实现方案 #

方案1：使用INSTR函数（简单替代） #

INSTR函数用于查找子串在目标字符串中的位置，若找到则返回大于0的数值，可直接替代LIKE '%xxx%'的模糊匹配逻辑：

SELECT *
FROM   company_details
WHERE  INSTR(company, :p_company) > 0;

注：:p_company为传入的参数，比如'892'或'682'。

方案2：使用REGEXP_INSTR（精确匹配单个分隔值） #

如果需要精确匹配逗号分隔的单个值（避免匹配到包含目标值的子串，比如避免把'892'匹配到'8923'），可以用正则表达式精准定位：

SELECT *
FROM   company_details
WHERE  REGEXP_INSTR(company, '(^|,)' || :p_company || '(,|$)') > 0;

正则表达式(^|,)匹配字符串开头或逗号，(,|$)匹配逗号或字符串结尾，确保参数是独立的分隔项。

方案3：将字符串转为集合匹配 #

通过XMLTABLE把逗号分隔的字符串拆分为集合，再用MEMBER OF判断参数是否属于集合中的元素：

SELECT cd.*
FROM   company_details cd
WHERE  :p_company MEMBER OF (
    SELECT CAST(MULTISET(
        SELECT TRIM(column_value)
        FROM   XMLTABLE(('"' || REPLACE(cd.company, ',', '","') || '"'))
    ) AS sys.odcivarchar2list)
);

这种方式同样实现精确匹配单个分隔值，适合对匹配精度要求高的场景。

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