sql中生成查询的模糊匹配字符串

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_Sql]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[f_Sql]
GO

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[序数表]')andOBJECTPROPERTY(id,N'IsUserTable')=1)
droptable[序数表]
GO

--为了效率,所以要一个辅助表配合
selecttop1000id=identity(int,1,1)into序数表
fromsyscolumnsa,syscolumnsb
altertable序数表addconstraintpk_id_序数表primarykey(id)
go

/*--根据指定字符串生成查询的模糊匹配字符串

条件连接的关键字为and,or
可以任意指定括号
生成的条件表达式为like模糊匹配

--邹建2004.08(引用请保留此信息)--*/

/*--调用示例

--调用示例
selectA=dbo.f_Sql('(WeborHTMLorInternet)and(ProgrammerorDeveloper)','content')
selectB=dbo.f_Sql('WeborHTMLorInternet','content')
selectC=dbo.f_Sql('(WebandHTML)','content')
selectD=dbo.f_Sql('Web','content')
--*/
--示例函数
createfunctionf_Sql(
@strNvarchar(1000),--要检索的字符串
@fdnamesysname--在那个字段中检索
)returnsNvarchar(4000)
as
begin
declare@rNvarchar(4000)
set@r=''
select@r=@r+case
whensubstring(@str,id,charindex('',@str+'',id)-id)in('or','and')
then''+substring(@str,id,charindex('',@str+'',id)-id)+''
whensubstring(@str,id,1)='('
then'(['+@fdname+']like''%'
+substring(@str,id+1,charindex('',@str+'',id)-id-1)
+'%'''
whensubstring(@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序数表
whereid<=len(@str)
andcharindex('',''+@str,id)-id=0
return(@r)
end
go

更多相关文章

随机推荐