while len(@expression1)>0 begin if charindex(',',@expression1)>0 begin select @str=left(@expression1,charindex(',',@expression1)-1) end else begin select @str=@expression1 select @expression1=@expression1+',' end if @expression2=replace(@expression,@str+',','') begin return -1 end select @expression1=replace(@expression1,@str+',','')end return 1 ------------- 粗略的写就是这样了,你在完善一下,逻辑肯定是行得通的。
测试通过!只的收藏吧?! create function GetRecords(@str varchar(8000)) returns @Rec table (id int IDENTITY (1,1), Record varchar(200)) as begin declare @s varchar(8000) declare @r varchar(200) declare @i int set @s=@str set @i=CHARINDEX(',',@s) while @i>0 begin set @r=left(@s,@i-1) insert @Rec values (@R) set @s=right(@s,len(@s)-@i) set @i=CHARINDEX(',',@s) end if len(@s)>0 insert @Rec values (@s) return endGO create function charIndexEx(@str1 varchar(8000),@str2 varchar(8000)) returns int as begin declare @r int if exists (select * from dbo.GetRecords(@str1) a,dbo.GetRecords(@str2) b where a.id=b.id and a.record=b.record) set @r= 1 else set @r= -1 return @r endGO select dbo.charIndexEx('1,,3','1,5,2,3,4') 1 select dbo.charIndexEx('1,,3','2,3,4') -1
调用: select *,dbo.charIndexEx('1,,3',table1.fieldname) as flag from table1
To CCEO() :我在SQL Server7里执行你的语句,报错,错误如下: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'function'.请问SQL Server7支持创建函数吗?
while len(@expression1)>0
begin
if charindex(',',@expression1)>0
begin
select @str=left(@expression1,charindex(',',@expression1)-1)
end
else
begin
select @str=@expression1
select @expression1=@expression1+','
end if @expression2=replace(@expression,@str+',','')
begin
return -1
end
select @expression1=replace(@expression1,@str+',','')end
return 1
-------------
粗略的写就是这样了,你在完善一下,逻辑肯定是行得通的。
create function GetRecords(@str varchar(8000))
returns @Rec table (id int IDENTITY (1,1), Record varchar(200))
as
begin
declare @s varchar(8000)
declare @r varchar(200)
declare @i int
set @s=@str
set @i=CHARINDEX(',',@s)
while @i>0
begin
set @r=left(@s,@i-1)
insert @Rec values (@R)
set @s=right(@s,len(@s)-@i)
set @i=CHARINDEX(',',@s)
end
if len(@s)>0
insert @Rec values (@s)
return
endGO
create function charIndexEx(@str1 varchar(8000),@str2 varchar(8000))
returns int
as
begin
declare @r int
if exists (select * from dbo.GetRecords(@str1) a,dbo.GetRecords(@str2) b where a.id=b.id and a.record=b.record)
set @r= 1
else
set @r= -1
return @r
endGO
select dbo.charIndexEx('1,,3','1,5,2,3,4')
1
select dbo.charIndexEx('1,,3','2,3,4')
-1
select *,dbo.charIndexEx('1,,3',table1.fieldname) as flag from table1
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'function'.请问SQL Server7支持创建函数吗?