关于一个SQL自定义函数,各位高手请进!
/*
说明:该函数欲实现的功能是传入4个字符串(@find,@Field,@find1,@Field1),其中@find与@find1可能均为空,此时函数的返回值1,若@find与@find1均不为空(在传参数的时候将@find与@find1任一为空的情况排除了),则分别在@Field中查找@find,在@Field1中查找@find1,若两者均查找到且@find在@Field中出现的位置与@find1在@Field1中出现的位置相等(@Field,@Field1的格式一致,如@Field为:'87,89',@Field1为:'10,11',@Field、@Field1中用','分隔)则返回1,否则返回0,
例如
dbo.fn_fulfill('89','87,89','10','10,10') 返回1
dbo.fn_fulfill('87','87,89','10','10,10') 返回1
dbo.fn_fulfill('90','87,89','10','10,10') 返回0
dbo.fn_fulfill('89','87,89','10','10,11') 返回0
dbo.fn_fulfill('','87,89','','10,11') 返回1
dbo.fn_fulfill('87','87','10','10') 返回1
*/
Create function fn_fulfill (@find varchar(200), @Field varchar(200),@find1 varchar(200), @Field1 varchar(200))
returns int
as
begin
declare @return int
if( @find='' and @find1='')
begin
set @return=1
end
else
begin
if (PATINDEX('%'+@find+'%',@Field) = PATINDEX('%'+@find1+'%',@Field1))
set @return=1
else
begin
-- if ()
-- begin
-- end
-- else
-- set @return=0
end
end
return(@return)
end

--print dbo.fn_fulfill('89','87,89','10','10,10')
--print dbo.fn_fulfill('87','87,89','10','10,10')

小弟初学SQL,以上是小弟写的,没有写完全,望各位高手不吝赐教,谢谢~



[解决办法]

SQL code

create function   fn_fulfill   (@find   varchar(200),   @Field   varchar(200),@find1   varchar(200),   @Field1   varchar(200)) returns   int as begin declare   @return   int declare @pos int,@pos1 intif(   @find=''   and   @find1='') begin   set   @return=1 end else begin     declare @t table(pos int)    declare @t1 table(pos int)         select @pos=charindex(','+@find+',',','+@Field+','),@pos1=charindex(','+@find1+',',','+@Field1+',')    if @pos>0 and @pos1>0    begin        while @pos>0        begin            insert @t select @pos            select @pos=charindex(','+@find+',',','+@Field+',',@pos+1)        end        while @pos1>0        begin            insert @t1 select @pos1            select @pos1=charindex(','+@find1+',',','+@Field1+',',@pos1+1)        end        if exists(select 1 from @t a,@t1 b where a.pos=b.pos)            set @return=1        else             set @return=0    end    else        set @return=0end     return(@return) end
[解决办法]
SQL code
create  function   fn_fulfill   (@find   varchar(200),   @Field   varchar(200),@find1   varchar(200),   @Field1   varchar(200)) returns   int as begin declare   @return   intdeclare @index intdeclare @index_old intset @return = 0set @index_old = 0set @index = 0if(   len(@find) > 0  and   len(@find1) > 0) begin    while 1=1         begin            set @index = charindex(@find,@field,@index)                    if @index = @index_old                 break            else                set @index_old  = @index                        if charindex(@find1,@Field1,@index) >  0             begin                set @return = 1                 break            end   --if    end; --while end else --if         set @return = 1      return(@return) end
[解决办法]
更正：
SQL code
create function   fn_fulfill   (@find   varchar(200),   @Field   varchar(200),@find1   varchar(200),   @Field1   varchar(200)) returns   int as begin declare   @return   intdeclare @index intdeclare @index_old intset @return = 0set @index_old = 0set @index = 0if(   len(@find) > 0  and   len(@find1) > 0) begin    while 1=1         begin            set @index = charindex(@find,@field,@index)                    if @index = @index_old                 break            else                set @index_old  = @index                        if charindex(@find1,@Field1,@index) = @index  --更正: > 0            begin                set @return = 1                 break            end   --if    end; --while end else --if         set @return = 1      return(@return) end 
 
[解决办法]
SQL code
Create function fn_fulfill (@find varchar(200), @Field varchar(200),@find1 varchar(200), @Field1 varchar(200)) returns   int as begin declare   @return   intset  @return=0if(@find=''   and   @find1='')     begin       set   @return=1     end else     begin       if   ( @find<>''   and   @find1<>'' and charINDEX(@find,@Field)   =   charINDEX(@find1,@Field1))           set   @return=1       else       begin           set   @return=0       end     end return(@return) end
[解决办法]
SQL code
Create   function   fn_fulfill   (@find   varchar(200),   @Field   varchar(200),@find1   varchar(200),   @Field1   varchar(200)) returns   int as begin declare   @return   int set @return = 0if(   @find=''   and   @find1='') begin   set   @return=1 end else begin   if   (charindex(','+@find+',',@Field+',')   =   (charindex(','+@find1+',',@Field1+',')      set   @return=1  end       return(@return) end
[解决办法]
SQL code
create  function   fn_fulfill   (@find   varchar(200),   @Field   varchar(200),@find1   varchar(200),   @Field1   varchar(200)) returns   int as begin     declare @ret int    if @find='' and @find1=''        begin            set @ret=1        end    else if @find is not null and @find1 is not null        begin            declare @f int,@f1 int            select @f=charindex(','+convert(varchar(100),@find)+',',','+convert(varchar(10),@Field)+',')                        select @f1=charindex(','+convert(varchar(100),@find1)+',',','+convert(varchar(10),@Field1)+',')            if @f=@f1 and @f<>0                begin                    set @ret=1                end            else                 begin                    set @ret=0                end        endreturn(@ret)end