if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[f_sql]’) and xtype in (n’fn’, n’if’, n’tf’))
drop function [dbo].[f_sql]
go
if exists (select * from dbo.sysobjects where id = object_id(n'[序数表]’) and objectproperty(id, n’isusertable’) = 1)
drop table [序数表]
go
–为了效率,所以要一个辅助表配合
select top 1000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
go
/*–根据指定字符串生成查询的模糊匹配字符串
条件连接的关键字为 and,or
可以任意指定括号
生成的条件表达式为 like 模糊匹配
–邹建 2004.08(引用请保留此信息)–*/
/*–调用示例
–调用示例
select a=dbo.f_sql(‘(web or html or internet) and (programmer or developer)’,’content’)
select b=dbo.f_sql(‘web or html or internet’,’content’)
select c=dbo.f_sql(‘(web and html)’,’content’)
select d=dbo.f_sql(‘web’,’content’)
–*/
–示例函数
create function f_sql(
@str nvarchar(1000), –要检索的字符串
@fdname sysname –在那个字段中检索
)returns nvarchar(4000)
as
begin
declare @r nvarchar(4000)
set @r=”
select @r=@r+case
when substring(@str,id,charindex(‘ ‘,@str+’ ‘,id)-id) in(‘or’,’and’)
then ‘ ‘+substring(@str,id,charindex(‘ ‘,@str+’ ‘,id)-id)+’ ‘
when substring(@str,id,1)='(‘
then ‘([‘+@fdname+’] like ”%’
+substring(@str,id+1,charindex(‘ ‘,@str+’ ‘,id)-id-1)
+’%”’
when substring(@str,charindex(‘ ‘,@str+’ ‘,id)-1,1)=’)’
then ‘[‘+@fdname+’] like ”%’
+substring(@str,id,charindex(‘ ‘,@str+’ ‘,id)-id-1)
+’%”)’
else ‘[‘+@fdname+’] like ”%’
+substring(@str,id,charindex(‘ ‘,@str+’ ‘,id)-id)
+’%”’
end
from 序数表
where id<=len(@str)
and charindex(‘ ‘,’ ‘+@str,id)-id=0
return(@r)
end
go