【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