函数名称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