if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_comp]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[f_comp] GO if exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [序数表] GO--创建处理的序数表,主要是解决效率问题,不然可以直接在函数中生成序数表 select top 8000 id=identity(int,1,1) into 序数表 from sysobjects a,syscolumns b go/*--字符串列表比较 判断两个数据列表字符串中,是否有任意一个数字相同 --邹建 2004.03(引用请保留此信息)--*//*--调用示例 --调用 select dbo.f_comp('1,22,5,8,23','22,55,67,45,89') --*/ create function f_comp( @str1 varchar(8000), @str2 varchar(8000) )returns bit as begin declare @re bit
if exists(select 1 from( select aa='%,'+substring(@str1,id,charindex(',',@str1+',',id)-id)+',%' from 序数表 where substring(','+@str1,id,1)=',' )a where ','+@str2+',' like aa) set @re=1 else set @re=0 return(@re) end go
假设a字段在table1中,b字段在table2中select m.* from table1 n,table2 m where m.b like '%'+n.a+'%'
drop function [dbo].[f_comp]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [序数表]
GO--创建处理的序数表,主要是解决效率问题,不然可以直接在函数中生成序数表
select top 8000 id=identity(int,1,1) into 序数表
from sysobjects a,syscolumns b
go/*--字符串列表比较 判断两个数据列表字符串中,是否有任意一个数字相同
--邹建 2004.03(引用请保留此信息)--*//*--调用示例 --调用
select dbo.f_comp('1,22,5,8,23','22,55,67,45,89')
--*/
create function f_comp(
@str1 varchar(8000),
@str2 varchar(8000)
)returns bit
as
begin
declare @re bit
if exists(select 1 from(
select aa='%,'+substring(@str1,id,charindex(',',@str1+',',id)-id)+',%'
from 序数表 where substring(','+@str1,id,1)=','
)a where ','+@str2+',' like aa)
set @re=1
else
set @re=0
return(@re)
end
go