SQL多关键字模糊查询

在项目当中，通常会用到用空格分隔的多个关键字进行查询，博主为此留下了此方法

 

if object_id('[tb]') is not null
drop table [tb]
create table [tb] (id int,name varchar(25))
insert into [tb]
select 1,'asdf富士康sfsaf'
union all select 2,'富士康sdaf跳楼'
union all select 3,'sdaf跳楼'
union all select 4,'asdf富士康as跳楼郭台铭asf'
union all select 5,'郭台铭asf'   

declare @name varchar(50),
@sql varchar(8000)
set @Name='富士康 跳楼 郭台铭'
set @sql=' (case when charindex('''+replace(@name,' ',''',name)>0 then 1 else 0 end)+(case when charindex(''')+''',name)>0 then 1 else 0 end)'
set @sql='select * from tb order by '+@sql+' desc'exec(@sql)

 

 

=================================SQL多关键字并操作=================================================
declare @name varchar(50),
@sql varchar(8000)
set @Name='大三男生 千米跑后 学校 继续'
set @sql=' charindex('''+replace(@name,' ',''',T1.Title)>0   and charindex(''')+''',T1.Title)>0'
set @sql='select * from DebriefInfo T1 where  '+@sql exec(@sql)