/*
说明:该函数欲实现的功能是传入4个字符串(@find,@Field,@find1,@Field1),[email protected]@find1可能均为空,此时函数的返回值1,[email protected]@find1均不为空([email protected]@find1任一为空的情况排除了),[email protected]@find,[email protected]@find1,[email protected]@[email protected]@Field1中出现的位置相等(@Field,@Field1的格式一致,[email protected]:'87,89',@Field1为:'10,11',@[email protected]','分隔)则返回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([email protected]+'%',@Field) = PATINDEX([email protected]+'%',@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(',[email protected]+',',',[email protected]+','),@pos1=charindex(',[email protected]+',',',[email protected]+',') if @pos>0 and @pos1>0 begin while @pos>0 begin insert @t select @pos select @pos=charindex(',[email protected]+',',',[email protected]+',',@pos+1) end while @pos1>0 begin insert @t1 select @pos1 select @pos1=charindex(',[email protected]+',',',[email protected]+',',@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