Tips & Tricks: Incrementing a number with padded zeros in SQL

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.

We Put our Heart into the 26th Annual Ride for Hea...
Big Data and Business Intelligence (BI)
WebSan Solutions is a Gold Certified Microsoft Partner

Multi-Award Winning

Gold Microsoft Partner

Microsoft Partner of the Year

Dynamics 365 Business Central

Microsoft Canada Impact Award

Business Applications

Microsoft Inner Circle

Business Applications
WebSan is a Microsoft Solutions Partner

© Copyright 2024 WebSan Solutions Inc.WEBSAN is a trademark of WebSan Solutions Inc. Registered in Canada and other countries.