版本一：按分隔符进行比较
算法思路：按分隔符截取搜索字符串循环与待搜索字符进行比较
复制代码 代码如下:
use [fly]
go
–参数：@instr 待搜索字串，@fndstr 搜索字串，@doc 分隔符
— 例：select dbo.fsearch(‘1,2,3,4,5,6′,’3,6,5,8,2′,’,’) 返回0-不匹配，返回1-匹配(@instr中有@fndstr内容)
–可以用于表搜索，如：
— select *,dbo.fsearch(str,’3,6,5,8,2′,’,’) as 是否匹配 from 表名
— @fndstr和@doc两个参数就需要你自己提供了，@instr可以是数据表里的某个待搜索字段名
create function cgf_fn_search(@instr varchar(500),@fndstr varchar(500),@doc varchar(5))
returns int
as
begin
declare @i int,@c varchar(500),@fstr varchar(500)
set @fstr = @fndstr
while(len(@fstr) > 0)
begin
set @i = charindex(@doc, @fstr)
if(@i = 0)
begin
if(charindex(@fstr,@instr) > 0)
return 1
else
return 0
end
else
begin
set @c = substring(@fstr,1,@i-1)
if(charindex(@c,@instr) > 0)
return 1
else
set @fstr = substring(@fstr,@i+len(@doc),len(@fstr))
end
end
return 0
end
版本二：逐字进行比较
算法思路：逐字截取搜索字符串循环与待搜索字符进行比较
复制代码 代码如下:
use [fly]
go
/****** object: userdefinedfunction [dbo].[cgf_fn_searchchar] script date: 09/03/2010 16:42:12 ******/
set ansi_nulls on
go
set quoted_identifier on
go
create function [dbo].[cgf_fn_searchchar](@instr varchar(500),@fndstr varchar(500))
returns int
as
begin
declare @i int,@f int,@c varchar(1)
set @i = 1
set @f = len(@fndstr)
while(@i <= @f)
begin
set @c = substring(@fndstr, @i, @i)
if(charindex(@c,@instr) > 0)
begin
return 1
end
set @i = @i + 1
end
return 0
end