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)