Incrementing an integer in SQL is easy; all you have to do is + 1 to the value. However, incrementing a number that has padded zeros is a little trickier, since it is probably a string value and not a number. Luckily with some type conversions, it becomes just as easy as incrementing an integer.
First thing you want to do is to get the length of the string, so you know how many padded zeros to add. You can do this by using the LEN function:
1. SELECT @Length = LEN(RTRIM(LTRIM(@Num)))
It is also a good idea to trim the value just in case there are extra spaces.
Next we can increment the number. Now we can't just do a + 1 to the value since it’s a string and doing a + 1 to a string would just concatenate the 1 to the end of the string. So let’s convert the string to an integer:
2. SET @tempNum = CONVERT(INT, @Num) + 1
Nice, now we have incremented the number. Now all we have to do is add those zeros back in and we're done. To do this, we first convert the number back to a string. Then we concatenate a bunch of zeros to the front of the number.
Last, using the RIGHT function we set the length of the string back to the original length, using the length we got earlier, so that we have the appropriate number of leading zeros. Here's how it looks:
3. SET @nextNum = CONVERT(VARCHAR(21), @tempNum)
SET @nextNum = RIGHT('000000000000000000000' + @nextNum, @Length)
There you have your incremented number!
By: Dustin Yee, Application Developer, WebSan Solutions Inc., a Microsoft Dynamics Silver Partner and a CDN top 100 solution provider for 2012.