数据库函数-判断两个字符串用逗号切割后是否有相互重合的值

函数名称isin

使用示例 isin(str1,str2)='1'

说明：str1,str2分别用逗号切割成多个值，如果str1切割的值与str2切割的值没有相同的，则返回‘0’；若至少有1个相等的值，则返回‘1’

oracle函数代码

create or replace function isin(a in varchar2, b in varchar2)
  return varchar2 is
  V_LENGTH  NUMBER;
  V_LENGTH1 NUMBER;
  V_TMP     VARCHAR2(1000);
  V_TMP1    VARCHAR2(1000);
  v_res     varchar2(10) := '0';

begin
  if (a is null or b is null) then
    return v_res;
  end if;
  if (a is not null and b is not null) then
    V_LENGTH  := LENGTH(a) - LENGTH(REPLACE(a, ',', '')) + 1;
    V_LENGTH1 := LENGTH(b) - LENGTH(REPLACE(b, ',', '')) + 1;
    FOR i IN 1 .. V_LENGTH LOOP
      SELECT REGEXP_SUBSTR(a, '[^,]+', 1, i) INTO V_TMP FROM DUAL;
      FOR j IN 1 .. V_LENGTH1 LOOP
        SELECT REGEXP_SUBSTR(b, '[^,]+', 1, j) INTO V_TMP1 FROM DUAL;
        DBMS_OUTPUT.put_line(V_TMP);
        if (V_TMP = V_TMP1) then
          v_res := '1';
          return v_res;
          exit;
        end if;
      END LOOP; 
    END LOOP;
    return v_res;
  end if;
end isin;

mysql函数代码：

 
CREATE FUNCTION isin (
    a VARCHAR (1000),
    b VARCHAR (1000)
) RETURNS VARCHAR(10)
BEGIN
    DECLARE
        V_LENGTH INT;

DECLARE
    V_LENGTH1 INT;

DECLARE
    V_TMP VARCHAR (1000);

DECLARE
    V_TMP1 VARCHAR (1000);

DECLARE
    v_res VARCHAR (10) DEFAULT '0';

DECLARE
    i int  DEFAULT 1;
DECLARE
    j int ;

IF (a IS NULL OR b IS NULL) THEN
    RETURN v_res;
END IF;
if(a is not null and b is not null) then
 set V_LENGTH = LENGTH(a) - LENGTH(REPLACE(a, ',', '')) + 1;
 set V_LENGTH1 = LENGTH(b) - LENGTH(REPLACE(b, ',', '')) + 1;
    while i<=V_LENGTH do 
        set j=1;
        select substring_index(substring_index(a,',',i),',',-1) into V_TMP;
        while j<=V_LENGTH1 do
            select substring_index(substring_index(b,',',j),',',-1) into V_TMP1;
            if(V_TMP=V_TMP1)    then
        set v_res = '1';
        return v_res;
      end if;
            set j= j+1;
        end while; 

        set i=i+1;

    end while;
RETURN v_res;
end if;

END