How to preserve leading 0 digits from an integer or varchar in SQL?

Question

My purpose is to add +1 so that the desired value became 001 + 1 == 002 but it returns me 2 instead of 002 Please see the below sample query

DECLARE @l_EmploymentID NVARCHAR(30)/ int
SELECT @l_EmploymentID = 001
SELECT LEN(@l_EmploymentID)-- output is 1

If I get the length as 3 then I can get the desired value 002 using REPLICATE function Please suggest me how can I get length as 3 or summing 1 with 001 = 002

Thanks in advance.

Answer 1

You can determine the number of proceeding zeros by calculating the difference in the number of characters of the original string by the number of characters after converting the original string to an INT. This difference can then be used in your REPLICATE function to prepare the prefix for your result.

DECLARE @l_EmploymentID varchar(30)
SELECT @l_EmploymentID = '001'
SELECT REPLICATE('0', LEN(@l_EmploymentID) - LEN(CAST(@l_EmploymentID AS INT))) + CAST(@l_EmploymentID + 1 as VARCHAR) --output is 002