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

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

2019-10-26 18:44mssql教程网 Sql Server

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