在SQL声明语句中使用字符串的一部分

Question

我想做一个查询，给出与下面所示相同的结果，但我只需要在SET语句中插入前4位数字，即" SET $campaign = '0029'"有没有办法在这样的字符串语句中填充通配符?

declare @campaignNo varchar(60)
set @campaignNo = '0029_old_Leu_2021_1'
Select Count(PUR1.intTimestamp) AS #start
From [MicrobialScreening].[dbo].[PUR_IDs] as ID 
INNER JOIN [MicrobialScreening].[dbo].[PUR_Purification1] AS PUR1 
ON id.intID=pur1.intID
where id.strCampaign = @campaignNo

Answer 1

也许LIKE是你想要的:

declare @campaignNo varchar(60)
set @campaignNo = '0029'
Select Count(PUR1.intTimestamp) AS #start
From [MicrobialScreening].[dbo].[PUR_IDs] as ID 
INNER JOIN [MicrobialScreening].[dbo].[PUR_Purification1] AS PUR1 
ON id.intID=pur1.intID
where id.strCampaign like @campaignNo+'%'

Answer 2

如果活动号是固定宽度的(如最初问题中所述的前四个)，您可以使用LEFT函数。

set @campaignNo = '0029'

…

where LEFT(id.strCampaign, 4) = @campaignNo

对于更复杂的解析，可以使用SUBSTRING或LIKE。如果您使用下划线来使用LIKE进行解析，请确保使用ESCAPE关键字来避免歧义。

where id.strCampaign = LIKE CONCAT(@campaignNo, '_%') ESCAPE ''