34,590
社区成员
发帖
与我相关
我的任务
分享
/*
declare @t table([id] int identity(1,1),number varchar(20),ordernumber varchar(50))
insert @t(number,ordernumber) select '10001','A,B,C%'
union all select '10002','KT%,0000,M'
union all select '10003','1,34,T,Y'
union all select '10004','I,O,P%'
select * from @t
/*
(所影响的行数为 4 行)
id number ordernumber
----------- -------------------- --------------------------------------------------
1 10001 A,B,C%
2 10002 KT%,0000,M
3 10003 1,34,T,Y
4 10004 I,O,P%
(所影响的行数为 4 行)
*/
select *
from @t
where charindex(','+cast(@s as varchar)+',',','+ordernumber+',')>0 and number='10001'
/*
id number ordernumber
----------- -------------------- --------------------------------------------------
1 10001 A,B,C%
(所影响的行数为 1 行)
*/
DECLARE @s varchar(30)
set @s='KTasdfla'
WITH CTE
(
SELECT ordernumber,1 as S ,CHARINDEX(',',ordernumber) as E
UNION ALL
SELECT ordernumber,b.E+2,CHARINDEX(',',a.ordernumber,b.E+2)-1
FROM table a INNER JOIN CTE b
ON a.ordernumber=b.ordernumber
AND CHARINDEX(',',a.ordernumber+',',b.E+2)>0
)
SELECT * FROM table WHERE @s LIKE SUBSTRING(ordernumber,S,E-S+1)
set nocount on
declare @t table([id] int identity(1,1),number varchar(20),ordernumber varchar(50))
insert @t(number,ordernumber) select '10001','A,B,C%'
union all select '10002','KT%,0000,M'
union all select '10003','1,34,T,Y'
union all select '10004','I,O,P%'
if object_id('Tempdb..#t') is not null drop table #t
if object_id('Tempdb..#') is not null drop table #
select top 100 ID=Identity(int,1,1) into #t from syscolumns a,syscolumns b
select a.number,ordernumber=substring(a.ordernumber,b.ID,charindex(',',a.ordernumber+',',b.ID)-b.ID)
into #
from @t a,#t b
where charindex(',',','+a.ordernumber,b.ID)=b.ID
declare @s varchar(20),@d varchar(20)
set @s='C'--'KT','K'
set @d='10001'--'10002'
select * from @t t
where exists(
select 1 from #
where number=t.number
and number=@d
and left(ordernumber,charindex('%',ordernumber+'%')-1)=left(@s,charindex('%',ordernumber+'%')-1))
set nocount off
/*
id number ordernumber
1 10001 A,B,C%
*/
declare @t table([id] int identity(1,1),number varchar(20),ordernumber varchar(50))
insert @t(number,ordernumber) select '10001','A,B,C%'
union all select '10002','KT%,0000,M'
union all select '10003','1,34,T,Y'
union all select '10004','I,O,P%'
if object_id('Tempdb..#t') is not null drop table #t
if object_id('Tempdb..#') is not null drop table #
select top 100 ID=Identity(int,1,1) into #t from syscolumns a,syscolumns b
select a.number,ordernumber=substring(a.ordernumber,b.ID,charindex(',',a.ordernumber+',',b.ID)-b.ID)
into #
from @t a,#t b
where charindex(',',','+a.ordernumber,b.ID)=b.ID
declare @s varchar(20),@d varchar(20)
set @s='C'--'KT','K'
set @d='10001'--'10002'
select * from #
where number=@d
and left(ordernumber,charindex('%',ordernumber+'%')-1)=left(@s,charindex('%',ordernumber+'%')-1)
declare @t table([id] int identity(1,1),number varchar(20),ordernumber varchar(50))
insert @t(number,ordernumber) select '10001','A'
union all select '10001','B'
union all select '10001','C%'
union all select '10002','KT%'
union all select '10002','0000'
union all select '10002','M'
declare @s varchar(20),@d varchar(20)
set @s='C'--'KT','K'
set @d='10001'--'10002'
select * from @t
where number=@d
and left(ordernumber,charindex('%',ordernumber+'%')-1)=left(@s,charindex('%',ordernumber+'%')-1)
declare @t table([id] int identity(1,1),number varchar(20),ordernumber varchar(50))
insert @t(number,ordernumber) select '10001','A,B,C%'
union all select '10002','KT%,0000,M'
union all select '10003','1,34,T,Y'
union all select '10004','I,O,P%'
select * from @t
declare @s varchar(20)
set @s='CKKK'
select *
from @t
where @s like PARSENAME(replace(ordernumber,',','.'),1)
OR @s like PARSENAME(replace(ordernumber,',','.'),2)
OR @s like PARSENAME(replace(ordernumber,',','.'),3)
OR @s like PARSENAME(replace(ordernumber,',','.'),4)
and number=10001
declare @t table([id] int identity(1,1),number varchar(20),ordernumber varchar(50))
insert @t(number,ordernumber) select '10001','A'
union all select '10001','B,C%'
union all select '10001','C%'
union all select '10002','KT%'
union all select '10002','0000'
union all select '10002','M'
declare @t table([id] int identity(1,1),number varchar(20),ordernumber varchar(50))
insert @t(number,ordernumber) select '10001','A,B,C%'
union all select '10002','KT%,0000,M'
union all select '10003','1,34,T,Y'
union all select '10004','I,O,P%'
declare @s varchar(50),@where varchar(50)
set @s='c%'
if right(@s,1)='%'
BEGIN
length=len(@s);
set @DimWhere=left(@s,length-1);
END
Else
@exactwhere=@s;
select *
from @t
where patindex(@DimWhere%,ordernumber)>0 or @exactwhere=ordernumber;
and number='10001'