我想要一个查询,返回两个字符串中的单词的顺序匹配数
例：
例：
表
Id column1 column2 result 1 'foo bar live' 'foo bar' 2 2 'foo live tele' 'foo tele' 1 3 'bar foo live' 'foo bar live' 0
select id,column1,column2,extractvalue(dbms_xmlgen.getxmltype('select cardinality (
sys.dbms_debug_vc2coll(''' || replace(lower(column1),' ',''',''' ) || ''') multiset intersect
sys.dbms_debug_vc2coll('''||replace(lower(column2),''' )||''')) x from dual'),'//text()') cnt
from table.
解决方法
就个人而言,在这种情况下,我会选择PL / sql代码而不是普通的sql.就像是：
包装规格：
create or replace package PKG is
function NumOfSeqWords(
p_str1 in varchar2,p_str2 in varchar2
) return number;
end;
包装体：
create or replace package body PKG is
function NumOfSeqWords(
p_str1 in varchar2,p_str2 in varchar2
) return number
is
l_str1 varchar2(4000) := p_str1;
l_str2 varchar2(4000) := p_str2;
l_res number default 0;
l_del_pos1 number;
l_del_pos2 number;
l_word1 varchar2(1000);
l_word2 varchar2(1000);
begin
loop
l_del_pos1 := instr(l_str1,' ');
l_del_pos2 := instr(l_str2,' ');
case l_del_pos1
when 0
then l_word1 := l_str1;
l_str1 := '';
else l_word1 := substr(l_str1,1,l_del_pos1 - 1);
end case;
case l_del_pos2
when 0
then l_word2 := l_str2;
l_str2 := '';
else l_word2 := substr(l_str2,l_del_pos2 - 1);
end case;
exit when (l_word1 <> l_word2) or
((l_word1 is null) or (l_word2 is null));
l_res := l_res + 1;
l_str1 := substr(l_str1,l_del_pos1 + 1);
l_str2 := substr(l_str2,l_del_pos2 + 1);
end loop;
return l_res;
end;
end;
测试用例：
with t1(Id1,col1,col2) as(
select 1,'foo bar live','foo bar' from dual union all
select 2,'foo live tele','foo tele' from dual union all
select 3,'bar foo live','foo bar live'from dual
)
select id1,col2,pkg.NumOfSeqWords(col1,col2) as res
from t1
;
结果：
ID1 COL1 COL2 RES
---------- ------------- ------------ ----------
1 foo bar live foo bar 2
2 foo live tele foo tele 1
3 bar foo live foo bar live 0
