sql中產生查詢的模糊比對字串

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