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

-- [sqlserver]在text类型字段中计算某一字符串出现的次数 declare @ table nvarchar ( 40 ) ; -- 查询表名 declare @ textfield nvarchar ( 40 ) ; -- 查询列名(text类型) declare @ findstr nvarchar ( 400 ) ; -- 查找的字符串(非空) declare @ step int

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

<span>declare <span>@</span><span>table</span> nvarchar<span>(</span>40<span>)</span><span>;</span> <span>-- 查询表名</span><br>declare <span>@</span>textfield nvarchar<span>(</span>40<span>)</span><span>;</span> <span>-- 查询列名(text类型)</span><br>declare <span>@</span>findstr nvarchar<span>(</span>400<span>)</span><span>;</span> <span>-- 查找的字符串(非空)</span><br>declare <span>@</span>step int<span>;</span> <span>-- 每次从text字段截取的字符串长度</span><br>declare <span>@</span>findcount int<span>;</span> <span>-- 出现次数</span><br>declare <span>@</span>sql nvarchar<span>(</span>400<span>)</span><span>;</span><br>declare <span>@</span>str nvarchar<span>(</span>4000<span>)</span><span>;</span><br>declare <span>@</span>findpos int<span>;</span><br>declare <span>@</span>pos int<span>;</span><br>declare <span>@</span>innerpos int<span>;</span><br><br><span>set</span> <span>@</span><span>table</span> <span>=</span> <span>'content'</span><span>;</span><br><span>set</span> <span>@</span>textfield <span>=</span> <span>'aboutus'</span><span>;</span><br><span>set</span> <span>@</span>findstr <span>=</span> <span>'<strong>'</strong></span><span>;</span><br><span>set</span> <span>@</span>step <span>=</span> 1000<span>;</span><br><span>set</span> <span>@</span>pos <span>=</span> 1<span>;</span><br><span>set</span> <span>@</span>findpos <span>=</span> 0<span>;</span><br><span>set</span> <span>@</span>findcount <span>=</span> 0<span>;</span><br><br>while<span>(</span><span>@</span>pos <span>=</span> 1 <span>or</span> len<span>(</span><span>@</span>str<span>)</span> <span>=</span> <span>@</span>step<span>)</span><br><span>begin</span><br>    <span>set</span> <span>@</span>sql <span>=</span> N<span>'select @str = substring(cast('</span><span>+</span><span>@</span>textfield<span>+</span><span>' as ntext), '</span><span>+</span><span>cast</span><span>(</span><span>@</span>pos <span>as</span> nvarchar<span>)</span><span>+</span><span>', '</span><span>+</span><span>cast</span><span>(</span><span>@</span>step <span>as</span> nvarchar<span>)</span><span>+</span><span>') from '</span><span>+</span><span>@</span><span>table</span><span>;</span><br>    <span>--select @sql;</span><br>    exec sp_executesql <span>@</span>sql<span>,</span> N<span>'@str nvarchar(4000) output'</span><span>,</span> <span>@</span>str output<span>;</span><br>    <span>--select @str;</span><br>    <span>set</span> <span>@</span>innerpos <span>=</span> 1<span>;</span><br>    while<span>(</span><span>@</span>innerpos <span>=</span> 1 <span>or</span> <span>@</span>findpos <span>></span> 0<span>)</span><br>    <span>begin</span><br>        <span>select</span> <span>@</span>findpos <span>=</span> charindex<span>(</span><span>@</span>findstr<span>,</span> <span>@</span>str<span>,</span> <span>@</span>innerpos<span>)</span><span>;</span><br>        <span>if</span> <span>@</span>findpos <span>></span> 0<br>            <span>set</span> <span>@</span>findcount <span>=</span> <span>@</span>findcount <span>+</span> 1<span>;</span><br>        <span>set</span> <span>@</span>innerpos <span>=</span> <span>@</span>findpos <span>+</span> len<span>(</span><span>@</span>findstr<span>)</span><span>;</span><br>    <span>end</span><br>    <span>set</span> <span>@</span>pos <span>=</span> <span>@</span>pos <span>+</span> len<span>(</span><span>@</span>str<span>)</span> <span>-</span> <span>(</span>len<span>(</span><span>@</span>findstr<span>)</span> <span>-</span> 1<span>)</span><span>;</span><br><span>end</span><br><br><span>select</span> <span>@</span>findcount<span>;</span> </span>