In the event you need to add leading zeros to an integer, the process is pretty straightforward. I am using the AdventureWorks database and SQL Server 2008 R2. For this example I will use the Sales.SalesPerson table for their SalesPersonID field which contains 3 digit IDs. First let’s take a look at the data. As you can see the data looks fine so let’s get started.
SELECT SalesPersonID FROM [AdventureWorks].[Sales].[SalesPerson]
The first thing we need to do is to pad the field with leading spaces using the STR function and specify 6 spaces.
SELECT STR(SalesPersonID, 6) 'SalesPersonID' FROM [AdventureWorks].[Sales].[SalesPerson]
Now we just need to replace those spaces with zeros using the REPLACE function
SELECT REPLACE(STR(SalesPersonID, 6),SPACE(1),'0') 'SalesPersonID' FROM [AdventureWorks].[Sales].[SalesPerson]
That’s it… nothing to it.