Use part of a string in SQL declare statement-CodePudding

I would like to make a query that gives the same result as the one shown below but where I only have to insert the first 4 digits in the SET statement i.e. "Set $campaignNo = '0029'" is there any way to fill in a wild card character in a string statement like that?

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

CodePudding user response：

Maybe LIKE is what you want:

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 '%'

CodePudding user response：

If the campaign number is fixed width (first four as stated in the initial question), you can use the LEFT function.

set @campaignNo = '0029'

...

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

For more complicated parsing, you can use SUBSTRING or LIKE. If you are using an underscore to parse with LIKE, make sure you use the ESCAPE keyword to avoid ambiguity.

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