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

ifexists(selectfromdbo.sysobjectswhereid=object_id(N'[dbo].[f_Sql]’)andxtypein(N’FN’,N’IF’,N’TF’))
dropfunction[dbo].[f_Sql]
GO ifexists(selectfromdbo.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)-id)+”
whensubstring(@str,1)='(‘
then'([‘+@fdname+’]like”%’
+substring(@str,id+1,id)-id-1)
+’%”’
whensubstring(@str,id)-1,1)=’)’
then'[‘+@fdname+’]like”%’
+substring(@str,id)-id-1)
+’%”)’
else'[‘+@fdname+’]like”%’
+substring(@str,id)-id)
+’%”’
end
from序数表
whereid<=len(@str)
andcharindex(”,”+@str,id)-id=0
return(@r)
end
go