Generate Sequential Data Padded With 0

Brad Arnold

We frequently have to generate location or item codes for clients. This is typically a sequential number that has multiple levels in the code. For example, there are 4 carousels; each carousel has either 23 or 46 carriages (a column of shelving); each carriage has 11 shelves; each shelf has either 3 or 6 possible locations.  We need barcodes for all possible locations.

Here is a sample of what this would look like for Carousel 1:

Sample_Carousel1

The issue here was I needed the data for the Carriage column to count from 01 to 46, but I needed each of those 46 numbers to be repeated 51 times. Because there are 11 shelves with 3 to 6 locations per shelf there are a total of 51 locations per Carriage.

Before I go further, I realize there are a number of ways to get this done. What follows was my simple and quick solution.

I decided to write a loop in a SQL Server query to generate my data.

Here is the SQL;

DECLARE @Val1 INT
DECLARE @Val2 INT
DECLARE @StrLen TINYINT

SET @StrLen = 2
SET @Val2 = 1

WHILE @Val2 <= 46
BEGIN
	SET @Val1 = 1

	WHILE @Val1 <= 51
	BEGIN
		IF LEN(@Val2) > @StrLen
			PRINT @Val2
		ELSE
			PRINT Replace(Str(@Val2, @StrLen), ' ', '0')

		SET @Val1 = @Val1 + 1
	END

	SET @Val2 = @Val2 + 1
END

Val1 = The number of times you want each number repeated.
Val2 = The total range of numbers you want to be generated.

For example, if you set Val1 to go from 1 to 51 and Val2 to go from 1 to 46 you will get 1 through 46 with each number repeated 51 times.

StrLen= The minimum length of the returned number. This will pad with 0 to make the returned value = whatever you set StrLen to. For example, if you set StrLen to 2, 1 – 9 would be 01 – 09. We do a check to see if the length of Val2 is > StrLen, if it is we print the value of Val2 without trying to pad it. That way 101 doesn’t come back as ** because it’s too long for StrLen.

The output looks something like this:

Print_Results

From here I can copy / paste the data into Excel where I then use a concatenate formula to generate my “Code” column data.