oracle语句实现一个字符串如“aa,bb”,是否包含在数据库表中的一个字段的内容中
该字段中可能存有的记录如下,如(p113字段):
p113
aa,bb,cc
aa,bb,cc,dd
aa,cc
bb,dd
语句如下:
select v from (select column_value v from table(split('aa,bb')))
where not exists(
select 1 from tem_propertie w where p113 is not null and instr(w.p113,v)>0 )
注:1)split方法
在Types文件夹下新建一类型,语句:
create
or
replace
type type_split
as
table
of
varchar2
(
4000
);
在Functions文件夹下新建一方法,语句:
代码
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;
(
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;
然后就可以使用split('aa,bb')调用方法
2)INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)
INSTR('CORPORATE FLOOR','OR', 3, 2)中,源字符串为'CORPORATE FLOOR', 目标字符串为'OR',起始位置为3,取第2个匹配项的位置
select substr(field1,instr(field1, '|', 1, rownum) + 1, instr(field1, '|', 1, rownum + 1) - instr(field1, '|', 1, rownum) - 1) as field2 from (select '|' || 'a|bbb|cccc|ddddd|ee|d|a' || '|' as field1 from dual) connect by instr(field1, '|', 2, rownum) > 0;
结果:a、bbb、cccc、ddddd、ee、d、a