模糊查询匹配
标题为：15ml Tapered Round Lotion Bottle
我在文本框输入：15ml Round Bottle 能够查询出标题里面有这几个词语的全部显示啊。
大侠们，sql 语句怎么写啊。想了一上午没想出啦。。 sql? 模糊查询
[解决办法]
create table tb(title varchar(100))
insert into tb
select '15ml Tapered Round Lotion Bottle' union all
select 'Rounds' union all
select '15'
go
declare @t varchar(100)
set @t = '15ml Round Bottle' --输入的字符串
;with t
as
(
select --v,
SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) vv
from
(
select replace(@t,' ',',') as v
)t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
)
select *
from tb
where exists (select 1
from t
where charindex(','+t.vv+',',','+replace(tb.title,' ',',')+',') > 0
)
/*
title
15ml Tapered Round Lotion Bottle
*/
create table u01
(标题 varchar(60))
insert into u01(标题)
select '15ml Tapered Round Lotion Bottle' union all
select 'title1 15ml Round' union all
select 'title2 Round Bottle'
declare @x varchar(60)
select @x='15ml Round Bottle'; --> 文本框输入内容
with t as
(select substring(a.s,b.number,charindex(' ',a.s+' ',b.number)-b.number) 'w'
from (select @x 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(' '+a.s,b.number,1)=' ')
select * from u01 a
where not exists(select 1 from t where charindex(w,a.标题,1)=0)
/*
标题
------------------------------------------------------------
15ml Tapered Round Lotion Bottle
(1 row(s) affected)
*/
