Sql Server 查找字符串的出现次数

有太多的“假设”来写一些完全满足你所有可能要求的东西，但是以下内容应该给你一个工作的想法：

select [value]
from String_Split('My conscience hath a thousand several tongues, And every tongue brings in a several tale, And every tale condemns me for a villain',' ')
group by value having count(*)>1


value
--------
a
And
every
several

这只是入门的一种方式，但请注意，这远非一个完整的解决方案，因为您需要定义单词的构成。这看似简单，但实际上非常困难。

例如，在我下面的脚本和输出中，您可以看到由于在空格字符上拆分而被视为一个单词。words,

所以去掉标点符号吧！很简单，对吧？那么，您将如何确保删除单词开头和结尾的单引号，例如忽略缩略词中的单引号？a sentence where a 'word' is quoteddon't replace this one

您将需要进行大量的预处理，或者接受您在这里尝试实现的任何内容都会得到很多不正确的输出。

---

查询

declare @t table(txt varchar(500));
insert into @t values
 ('My conscience hath a thousand several tongues, And every tongue brings in a several tale, And every tale condemns me for a villain.')
,('Another test string with no repeated words so that there are more values to show how this could work on an entire dataset')
,('And another that does repeat words, so that those words, where they match can be aggregated')
;

select t.txt
      ,s.[value] as word
      ,count(s.[value]) as occurances
from @t as t
    cross apply string_split(t.txt,' ') as s
group by t.txt
        ,s.[value]
having count(s.[value]) > 1;

输出

对不起，我之前误解了你的问题，所以我正在修改我的答案。 首先创建将接受字符串作为参数的存储例程。

DELIMITER //
create function `find_occurances`(string varchar(192))
returns varchar(192) deterministic
begin
    declare substr varchar(191) default "";
    declare occurances int default 0;
    declare length int default 0;
    declare temp_str varchar(191) default string;
    DELETE FROM occurances_in_sentence;
    create temporary table if not exists occurances_in_sentence(value varchar(51),occurances int);
    set length = round((length(string) - length(Replace(string," ","")))/1);
    while length >= 0 do
        set substr = SUBSTRING_INDEX(string," ",1);
        if(length(substr) = 0)then 
            return substr;
        end if;
        set occurances=round((length(string) - length(Replace(string,substr,"")))/length(substr));
        insert into occurances_in_sentence values (substr,occurances);
        set string = Replace(string,substr,"");
        set string = trim(string);
        set length = length -1;
    end while;
    return 1;
end//
DELIMITER ;

成功创建函数后。使用字符串调用函数。

select find_occurances("This is testing of every occurance in sentence of every number") as sub;

它的作用是创建一个结果的临时表。

select * from occurances_in_sentence;