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.