if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getcharcount]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[f_getcharcount]

GO

--得到一个字符串在另一个字符串中出现的次数

create function f_getcharcount(

@str varchar(8000),

@chr varchar(20)

) returns int

as

begin

declare @re int,@i int

select @re=0,@i=charindex(@chr,@str)+1

while @i>1

    select @re=@re+1

        ,@str=substring(@str,@i,8000)

        ,@i=charindex(@chr,@str)+1

return(@re)

end

go

--调用示例

select dbo.f_getcharcount('aadddbbbbad','ad')