需要实现一个自定义函数用于判断两个逗号分隔的字符串参数是否存在交集

DROP FUNCTION IF EXISTS has_intersection;
DELIMITER ;;
CREATE FUNCTION has_intersection(tag1 VARCHAR(1024), tag2 VARCHAR(1024)) RETURNS tinyint(1)
BEGIN
DECLARE output TINYINT(1);
IF tag1 IS NULL OR tag1 = '' OR tag2 IS NULL OR tag2 = '' THEN
	RETURN 1;
END IF;
SET output =  concat(",", tag1, ",") regexp concat(",", replace(tag2,',',',|,'),",");
RETURN output;
END;;
DELIMITER ;

不知道对不对，你测试下


CREATE OR REPLACE FUNCTION HAS_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,2,3,4',',5,6,7')
返回0

SELECT HAS_INTERSECTION('1,6,3,4','5,6,7')
返回1

SELECT HAS_INTERSECTION('','')
返回1