Pad then Add Leading Zeros

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s