問題描述

我想使用 ContainsTable 獲取嵌入在名為 description 的 t-sql nvarchar 列中的單個單詞的計數(shù).如果我提供紅色"或綠色"的標準，我怎么知道哪個真正匹配?簡而言之，我正在嘗試進行字數(shù)統(tǒng)計，并且正在尋找最佳方法.

I want to use ContainsTable to get counts on individual words embedded in a t-sql nvarchar column called description. If I provide the criteria of Red Or Green, how can I tell which one actually matched off? In short, i am trying to do word counts and am looking for the best approach.

提前致謝

推薦答案

給你:

drop function dbo.CountOccurrencesOfWord
go

create function dbo.CountOccurrencesOfWord(
    @text varchar(max),
    @word varchar(8000)
)
returns int
as 
begin 
    declare 
        @index int = charindex(@word, @text, 1),
        @len int = len(@word),
        @count int = 0
    while @index > 0  begin             
        set @count = @count + 1
        set @index = charindex(@word, @text, @index + @len)
    end
    return @count
end

GO

if object_id('tempdb..#example') is not null
    drop table #example
create table #example(
    description nvarchar(4000) not null
)

insert into #example select 'red yellow green red white blue red redred red green red'
insert into #example select 'red yellow green red'
insert into #example select 'orange grey green'
insert into #example select ''
insert into #example select 'magenta aqua cyan'

select dbo.CountOccurrencesOfWord(description, 'red'), description
from #example

注意事項 - 這種邏輯在 t-sql 中可能非常昂貴.

word of caution- this kind of logic can be quite costly in t-sql.

這篇關(guān)于t-sql:計算 varchar 列中單詞的出現(xiàn)次數(shù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！