【DM】判断两个逗号分隔的字符串参数是否存在交集
CREATE OR REPLACE FUNCTION SIGN_INTERSECTION( V_TAG1 VARCHAR2, V_TAG2 VARCHAR2 ) RETURN INTEGER IS BEGIN IF V_TAG1 IS NULL OR V_TAG2 IS NULL OR V_TAG1 = '' OR V_TAG2 = '' THEN RETURN 1; END IF; --去掉前缀和尾随逗号 V_TAG2 := RTRIM(LTRIM(V_TAG2,','),','); --逗号转换为|,对应正则表达式或运算 V_TAG2 := '\,'||REPLACE(V_TAG2,',','\,|\,')||'\,'; RETURN SIGN(REGEXP_INSTR(','||V_TAG1||',',V_TAG2)); END; --测试 SELECT HAS_INTERSECTION('1,2,3,4','5,6,7'); --返回0 SELECT HAS_INTERSECTION('1,6,3,4','5,6,7'); --返回1 SELECT HAS_INTERSECTION('',''); --返回1