SQL: How to pad or add right side of a number with zeros (0)

This morning, I was writing an invoice number generator module for my open source billing system project that will be released soon at foxtrot0911.com. The format of the invoice number is YEAR+NNNNN(Ex. 200900001). The five right most numbers are incremental and will start at 1. I decided to put the invoice number generator on a store procedure and the challenge was, How will I put leading zeros to number to make a fixed width of numbers? (Example: 1 to 00001, 2 to 00002... 20 to 00020 and so on).

Today I will show you how you can implement it using SQL stored procedure. There are several way to do this in SQL, Here are two SQL script examples that you can use:

Using RIGHT function

SELECT @InvoiceNumber=RIGHT('00000' + CAST(InvoiceNo AS VARCHAR(5)), 5)
FROM TMPNumbers

/*Increment invoice number holder */
UPDATE TMPNumbers SET InvoiceNo=InvoiceNo+1

GO

Using RIGHT AND REPLICATE function

SELECT @InvoiceNumber=RIGHT(REPLICATE('0', 5) + CAST(InvoiceNo AS VARCHAR(5)), 5)
FROM TMPNumbers

/*Increment invoice number holder */
UPDATE TMPNumbers SET InvoiceNo=InvoiceNo+1

GO

For more SQL Coding Tips and Tricks, subscribe now