场景:字符串a,b,对a二分词后的字符串数组在b中出现的数目。只针对中文情况:
delimiter $$
use `database-X`$$
drop function if exists `wordCount`$$
create function `wordCount`(a text,b text) returns int(11)
begin
declare text_a text ;
declare text_b text ;
declare len int;
declare sumWord int;
declare idex int;
declare temp text;
declare assistant text;
set assistant = '我我我';
set text_a = trim(a);
set text_b = trim(b);
set len = char_length(text_a)-1;
set sumWord = 0;
set idex = 1;
while idex <= len do
set temp = substring(text_a,idex,2);
set idex = idex +1;
set sumWord = sumWord + char_length(replace(text_b,temp,assistant))- char_length(text_b) ;
end while;
return sumWord;
end$$
delimiter ;
测试一下:
select wordCount('我们搜索','我们搜索不知道啊我们搜索索搜索')
wordCount('我们搜索','我们搜索不知道啊我们搜索索搜索')
7