Formatting numbers to add leading zeros can be done in SQL Server. It is just simple. Lets create a new table and see how it works:
CREATE TABLE Numbers(Num INT);
Table Created.
Lets insert few values and see:
INSERT Numbers VALUES('12');
INSERT Numbers VALUES('112');
INSERT Numbers VALUES('12');
INSERT Numbers VALUES('122');
INSERT Numbers VALUES('122');
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
Now we can see how the numbers are formatted with 6 digits, if it has less than 6 digits it will add leading zeros.
Data:
SELECT * FROM Numbers;
Num
12
112
12
122
122
5 row(s) affected.
Formatting:
SELECT RIGHT('00000'+ CONVERT(VARCHAR,Num),6) AS NUM FROM Numbers;
NUM
000012
000112
000012
000122
000122
5 row(s) affected.