[sqlserver]textタイプフィールドで文字列の出現回数を検索します。
8022 ワード
-- [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;