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
