37.生成查询的模糊匹配字符串

不念不忘少年蓝@ 2024-02-19 17:51 139阅读 0赞

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 模糊匹配
    
    */
    
    /*--调用示例
    
    	--调用示例
    	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