Dropping leading zeros when adding 1 to a 3 pos char column

[DWSRAMBO]

Greetings, I have a question regarding what I think is probably a fairly basic question but through a couple hours of trying various things (including cast, convert, and a host of others) I have been unable to come up with an answer.
For this example I have cut pertinent information out of the actual Query. I have a 3 position character field which I wish to add "1" too. I would like it to be represented as "001" before the addition and "002" after the addition;however in everything I have tried the leading zeros are dropped after the addition.

Sample:

DECLARE  @financialPartyID	char(3)

SELECT @financialPartyID = '001'
print '@financialPartyID Field Display:'+@financialPartyID
SELECT @financialPartyID = cast(cast(@financialPartyID + 1 as numeric )as char(3))
print '@financialPartyID Field Display:'+@financialPartyID

Result(S)

@financialPartyID Field Display: 001
@financialPartyID Field Display: 2

Thanks
Rich

 

[ClaireHCM]

Try this~~~~

DECLARE @financialPartyID int
set @financialPartyID = 1
declare @string varchar(10)
set @string = replicate(0,3-len(@financialPartyID))+convert(varchar(3),@financialPartyID)
print '@financialPartyID Field Display:'+@string
SELECT @financialPartyID = @financialpartyid +1
set @string = replicate(0,3-len(@financialPartyID))+convert(varchar(3),@financialPartyID)
print '@financialPartyID Field Display:'+@string

 

[ClaireHCM]

Another way(similar to what you provided as example)

DECLARE @financialPartyID char(3)
set @financialPartyID = '001'
print '@financialPartyID Field Display:'+@financialPartyID
declare @int int
set @int = convert(int,@financialpartyid)
set @int = @int+1
set @financialpartyid = replicate(0,3-len(@int))+convert(varchar(3),@int)
print '@financialPartyID Field Display:'+@financialPartyID