ayesamson

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]

Padding

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]

Replacing

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.