SQL Server: How to Left Pad a Number with Zeros

The replicate T-SQL function in Microsoft’s SQL Server makes it simple to add leading zeros to a number value.

Let’s create a table ‘emp’ with empid and salary columns.

create table emp
(
empid int,
salary int
);

 
Insert some sample data into table ‘emp’.

insert into emp values (1,300);
insert into emp values (2,30);
insert into emp values (3,500000);
insert into emp values (4,1000);

 

select * from emp;

empid       salary
----------- -----------
1           300
2           30
3           500000
4           1000

(4 row(s) affected)

 
Now, let us left pad the salary column with 0s.

If we don’t know how many 0s we need to pad to the left so that all the values would be equal length, we can find the max length of salary column using following query:

SELECT Max(mylength)
FROM   (SELECT Len(salary) AS mylength
        FROM   emp) x;

-----------
6

 

The query returns a value of 6 which is the max length in salary column due to empid 3 with 500000 salary.

Now to add the leading zeros to the salary column, we can use the replicate inbuilt string function in T-SQL.

Syntax:

replicate(string expression, integer expression);

 
SQL Server 2008: The below query will left pad salary column with 0s.

select empid,          
       replicate('0', 6 - len(salary)) + cast (salary as varchar) as salary
from   emp;  

empid       salary
----------- -----------
1           000300
2           000030
3           500000
4           001000

(4 row(s) affected)

 
In the SQL Server 2008 version query, the first parameter is our ‘0’ padding string. In the second parameter we are subtracting the length of salary column from 6 which is our max length. Thus, our expression knows how many 0s are needed to left pad and concatenate the salary column.

With SQL Server 2012 you can achieve the same output with a simple one line code using FORMAT function.

SQL Server 2012: Left pad salary column with 0s

SELECT FORMAT (salary, '000000') FROM emp;

empid       salary
----------- -----------
1           000300
2           000030
3           500000
4           001000

(4 row(s) affected)
 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

  • Malcolm

    There are a couple of other ways to left pad with zeros – if you have SQL Server 2008 R2 or later, then
    format (empid, ‘000000’)
    is all you need to do. Format can be used with numbers or dates – a simple way to make data pretty.
    The older method
    right(‘000000′ + cast (salary as varchar), 6) also works fine.

    • Vishwanath Dalvi

      @Malcolm Thank you, Yeah you could achieve this output many ways, but using replicate i found it easy, and FORMAT function is newly introduced in SQL Server 2012 and it works to left pad a number with zeros.

  • binardfr

    Thank you, I didn’t know replicate function. For information, it works on SQL Server 2005