如题:
例如:str1='1,2,35,3'
str2='2,3' 则为true
总之,将str1以逗号分割,然后将str2以逗号分割,两个字符串只要其中的一个字符是一样的,就返回true,否则返回false。SQL该怎么实现?
例如:str1='1,2,35,3'
str2='2,3' 则为true
总之,将str1以逗号分割,然后将str2以逗号分割,两个字符串只要其中的一个字符是一样的,就返回true,否则返回false。SQL该怎么实现?
--true
else
--false
end if;
先建一个分割函数,后续你自己处理
create or replace type type_split as table of varchar2(100); --创建一个 type ,如果为了使split函数具有通用性,请将其size 设大些。
--创建function
create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2(50) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;
SELECT '1,2,35,3' AS STR1,'2,3' AS STR2 FROM DUAL
UNION ALL
SELECT '1,2,35,3' AS STR1,'4,6,8' AS STR2 FROM DUAL
)SELECT STR1, STR2, DECODE(FLG, 0, 'TRUE', 'FALSE') AS RESULT
FROM (SELECT INSTR(TRANSLATE(REPLACE(STR1, ','),
REPLACE(STR2, ','),
'XXXXXXXXXX'),
'X') AS FLG,
STR1,
STR2
FROM TEST)--------------------------------------------
1 1,2,35,3 2,3 FALSE
2 1,2,35,3 4,6,8 TRUE
3 1,2,35,3,8 4,6,8 FALSE
select regexp_substr('2,3','\d+',1,level) s1
from dual connect by level <= (select length(regexp_replace('2,3','\d+'))+1 from dual)
intersect
select regexp_substr('1,2,35,3','\d+',1,level) s1
from dual connect by level <= (select length(regexp_replace('1,2,35,3','\d+'))+1 from dual)
)