declare @str nvarchar(4000) set @str='油封,其它';with b as ( select substring(@str,number,charindex(',',@str+',',number)-number) as Str1 from master.dbo.spt_values where type='P' and SUBSTRING(','+@str,number,1)=',' )select 规格名称 from dhbz as a where exists(select 1 from b where a.规格名称 like '%'+Str1+'%'
用存储过程处理吧 declare @str nvarchar(4000) set @str='油封,其它'select top 100 number=identity(int,1,1) into # from syscolumnsselect substring(@str,number,charindex(',',@str+',',number)-number) as Str1 into #2 from # where type='P' and SUBSTRING(','+@str,number,1)=','select 规格名称 from dhbz as a where exists(select 1 from #2 where a.规格名称 like '%'+Str1+'%')
楼主自己测一下结果 declare @str nvarchar(4000) set @str='油封,其它'if OBJECT_ID('Tempdb..#') is not null drop table # select top 100 number=identity(int,1,1) into # from syscolumnsif OBJECT_ID('Tempdb..#2')is not null drop table #2 select substring(@str,number,charindex(',',@str+',',number)-number) as Str1 into #2 from # where type='P' and SUBSTRING(','+@str,number,1)=','select 规格名称 from dhbz as a where exists(select 1 from #2 where a.规格名称 like '%'+Str1+'%')
还是编译不了,其实我的想法很单纯,只想从表dhbz中查询出规格名称,要求包含[第一特征] 和 [其他特征] 只是[其他特征]中有逗号分隔符,分隔符表示或 如: insert into dhbz(规格名称,第一特征,其他特征) values('油封12*18*3 200T010006000A0','油封','12*18*3,200t010006000')select 规格名称 from dhbz where 第一特征 like'%油封%' and (其他特征 like'%12*18*3%' or 其他特征 like'%200t010006000%')
declare @str nvarchar(4000) set @str='油封,其它'if OBJECT_ID('Tempdb..#') is not null drop table # select top 100 number=identity(int,1,1) into # from syscolumnsif OBJECT_ID('Tempdb..#2')is not null drop table #2 select substring(@str,number,charindex(',',@str+',',number)-number) as Str1 into #2 from # where SUBSTRING(','+@str,number,1)=','select 规格名称 from dhbz as a where exists(select 1 from #2 where a.规格名称 like '%'+Str1+'%')看看是不是这样?
上面语句 #--临时表生成一个序号 1~100#2把字符拆分为一个表select--把拆分表作为条件用如果是同一行的条件判断可以这样用select * from [dhbz] as a where 规格名称 like '%'+第一特征+'%'+replace([其他特征],',','%')+'%'
declare @str nvarchar(4000)
set @str='油封,其它';with b
as
(
select
substring(@str,number,charindex(',',@str+',',number)-number) as Str1
from master.dbo.spt_values
where type='P' and SUBSTRING(','+@str,number,1)=','
)select 规格名称 from dhbz as a where exists(select 1 from b where a.规格名称 like '%'+Str1+'%'
用存储过程处理吧
declare @str nvarchar(4000)
set @str='油封,其它'select top 100 number=identity(int,1,1) into # from syscolumnsselect
substring(@str,number,charindex(',',@str+',',number)-number) as Str1
into #2
from #
where type='P' and SUBSTRING(','+@str,number,1)=','select
规格名称 from dhbz as a
where exists(select 1 from #2 where a.规格名称 like '%'+Str1+'%')
declare @str nvarchar(4000)
set @str='油封,其它'if OBJECT_ID('Tempdb..#') is not null
drop table #
select top 100 number=identity(int,1,1) into # from syscolumnsif OBJECT_ID('Tempdb..#2')is not null
drop table #2
select
substring(@str,number,charindex(',',@str+',',number)-number) as Str1
into #2
from #
where type='P' and SUBSTRING(','+@str,number,1)=','select
规格名称 from dhbz as a
where exists(select 1 from #2 where a.规格名称 like '%'+Str1+'%')
只是[其他特征]中有逗号分隔符,分隔符表示或
如:
insert into dhbz(规格名称,第一特征,其他特征) values('油封12*18*3 200T010006000A0','油封','12*18*3,200t010006000')select 规格名称 from dhbz where 第一特征 like'%油封%' and (其他特征 like'%12*18*3%' or 其他特征 like'%200t010006000%')
set @str='油封,其它'if OBJECT_ID('Tempdb..#') is not null
drop table #
select top 100 number=identity(int,1,1) into # from syscolumnsif OBJECT_ID('Tempdb..#2')is not null
drop table #2
select
substring(@str,number,charindex(',',@str+',',number)-number) as Str1
into #2
from #
where SUBSTRING(','+@str,number,1)=','select
规格名称 from dhbz as a
where exists(select 1 from #2 where a.规格名称 like '%'+Str1+'%')看看是不是这样?
#--临时表生成一个序号 1~100#2把字符拆分为一个表select--把拆分表作为条件用如果是同一行的条件判断可以这样用select *
from [dhbz] as a where 规格名称 like '%'+第一特征+'%'+replace([其他特征],',','%')+'%'