在此通过info表中的name字段来实现分段的匹配查询。
如表info中的name为'test1a234*23dewrdfe'
现在想通过输入test1,234*23,dewr来实现查询。
如:
declare @Name as nvarchar(100)
set @Name =为'test1a234*23dewrdfe'
select Info.* from Info where InfoName like '%'+'test1'+'%' and InfoName like '%'+234*23+'%'+ and InfoName like '%'+dewr+'%'。
试问改怎样实现呢?
如表info中的name为'test1a234*23dewrdfe'
现在想通过输入test1,234*23,dewr来实现查询。
如:
declare @Name as nvarchar(100)
set @Name =为'test1a234*23dewrdfe'
select Info.* from Info where InfoName like '%'+'test1'+'%' and InfoName like '%'+234*23+'%'+ and InfoName like '%'+dewr+'%'。
试问改怎样实现呢?
InfoName like '%test1%' and
InfoName like '%234*23%' and
InfoName like '%dewr%'
declare @str1 nvarchar(20),@str2 nvarchar(20),@str3 nvarchar(20)
set @str1 = N'test1'
set @str2 = N'234*23'
set @str3 = N'dewr'SELECT * FROM Info WHERE
patindex(N'%' + @str1 + N'%',InfoName) > 0 and
patindex(N'%' + @str2 + N'%',InfoName) > 0 and
patindex(N'%' + @str3 + N'%',InfoName) > 0
CHARINDEX('wondrous', notes)
select Info.* from Info
where CHARINDEX('test1', InfoName) >0
and CHARINDEX('234*23', InfoName) >0
and CHARINDEX('dewr', InfoName) >0
where CHARINDEX('test1', InfoName) >0
and CHARINDEX('234*23', InfoName) >0
and CHARINDEX('dewr', InfoName) >0
declare @Name as nvarchar(100)
set @Name ='test1,234*23,dewr'(其中个数并不确定)根据此来查询应该是是如下形式:
select * from Info where InfoName like '%'+@Name +'%'。
但是我想得到等同于如下的效果的查询:
select Info.* from Info where InfoName like '%'+'test1'+'%' and InfoName like '%'+234*23+'%'+ and InfoName like '%'+dewr+'%'。
set @Name ='test1,234*23,dewr'
declare @sql nvarchar(4000)
set @sql='select * from Info where InfoName like'
select @sql=@sql+' ''%'+replace(@Name,',','%'' and InfoName like ''%')+'%'''
print @sql
exec(@sql)---------------------select * from Info where InfoName like '%test1%' and InfoName like '%234*23%' and InfoName like '%dewr%'