--测试数据
create table t1(a text)
insert t1 select '案发司法速读法速读法司法速读法速度发来了吉林两岁凤飞飞速读法速读法速读法'
insert t1 select '案发司法速读法速读法司法速读法速度发来了吉林两岁凤飞飞速读法速读法速读法'
--创建查询函数
create function f_find(@str varchar(100))
returns int
as begin
declare @a int
declare @b int
declare @c int
declare @d int
declare @e int
declare @str1 varchar(8000)
set @a=0
set @c=0
set @d=1
set @e=1
set @str1=''
while @d<=(select (datalength(a)/3500)+1 from t1) begin
select @str1=substring(a,@e,3500) from t1
set @b=1
while @b>0 begin
select @a=charindex(@str,@str1,@b)
set @b=@a
if @b>0 begin
set @c=@c+1
set @b=@b+1
end
end
set @d=@d+1
set @e=@e+3500
end
return @c
end
returns int
as begin
declare @a int
declare @b int
declare @c int
declare @d int
declare @e int
declare @str1 varchar(8000)
set @a=0
set @c=0
set @d=1
set @e=1
set @str1=''
while @d<=(select (datalength(a)/3500)+1 from t1) begin
select @str1=substring(a,@e,3500) from t1
set @b=1
while @b>0 begin
select @a=charindex(@str,@str1,@b)
set @b=@a
if @b>0 begin
set @c=@c+1
set @b=@b+1
end
end
set @d=@d+1
set @e=@e+3500
end
return @c
end
--调用
select dbo.f_find('发')
/*结果
-----------
2
2
(所影响的行数为 1 行)
*/
*/
本文引用通告地址: http://blog.csdn.net/xueguang/services/trackbacks/511842.aspx