[sqlserver]在text类型字段中查找字符串出现次数

-- [sqlserver]在text类型字段中计算某一字符串出现的次数

declare @table nvarchar(40); -- 查询表名
declare @textfield nvarchar(40); -- 查询列名(text类型)
declare @findstr nvarchar(400); -- 查找的字符串(非空)
declare @step int; -- 每次从text字段截取的字符串长度
declare @findcount int; -- 出现次数
declare @sql nvarchar(400);
declare @str nvarchar(4000);
declare @findpos int;
declare @pos int;
declare @innerpos int;

set @table = 'content';
set @textfield = 'aboutus';
set @findstr = '<strong>';
set @step = 1000;
set @pos = 1;
set @findpos = 0;
set @findcount = 0;

while(@pos = 1 or len(@str) = @step)
begin
????set @sql = N'select @str = substring(cast('+@textfield+' as ntext),'+cast(@pos as nvarchar)+','+cast(@step as nvarchar)+') from '+@table;
????--select @sql;
????exec sp_executesql @sql, N'@str nvarchar(4000) output', @str output;
????--select @str;
????set @innerpos = 1;
????while(@innerpos = 1 or @findpos > 0)
????begin
????????select @findpos = charindex(@findstr, @str, @innerpos);
????????if @findpos > 0
????????????set @findcount = @findcount + 1;
????????set @innerpos = @findpos + len(@findstr);
????end
????set @pos = @pos + len(@str) - (len(@findstr) - 1);
end

select @findcount;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!