22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @code1 VARCHAR(20),@code2 VARCHAR(20),@d DATETIME
SET @d = '2017-10-16'
SET @code1='SR801P5000'
SET @code2=STUFF(@code1,6,1,'C')
/*
PRINT @code2
--SR801C5000
*/
SELECT * FROM t WHERE d=@d AND code1=@code1
SELECT * FROM t WHERE d=@d AND code2=@code2
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([a] Date,[b] nvarchar(30))
Insert #T
select '20170922',N'SR801P6000' union all
select '20170922',N'SR801P6500' union all
select '20170922',N'SR801C6000' union all
select '20170923',N'SR802P6000' union all
select '20170923',N'SR803P6500' union all
select '20170923',N'SR802C6000'
Go
--测试数据结束
SELECT *
FROM #T
WHERE b IN ( SELECT CASE WHEN b LIKE '%C%' THEN REPLACE(b, 'C', 'P')
ELSE REPLACE(b, 'P', 'C')
END
FROM #T
WHERE b IN ( 'SR801P6000', 'SR802C6000' ) ) --这两个是已经的红色的那个