How to implement leading zero to the variable in the SQL Server/Azure

Leading zero to the variable in SQL Server

To implement leading zero to the variable in SQL Server 2008, we can use following script. In some scenario we have to leading zero to the selected values from SQL server table as per the number of digits in the selected value.

 Create a table in SQL Server  

CREATE TABLE Employee(Code INT);

We can insert values in to the age column of the Employee table
    INSERT Employee VALUES(’13′);
    INSERT Employee VALUES(’111′);
    INSERT Employee VALUES(’12′);
    INSERT Employee VALUES(’322′);
    INSERT Employee VALUES(’422′);

1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.

 Select Code from Employee table

 SELECT * FROM Employee;

 Code
13
111
12
322
422

5 row(s) affected.

 How to Format column value foor leading zero in SQL Server

Now we are going to implement leading zero to each code up to the 4 digits.

 SELECT RIGHT('0000'+ CONVERT(VARCHAR,Code),4) AS EmployeeCode FROM Employee;

 EmployeeCode
0013
0111
0012
0322
0422

 If we want to format with 6 digits can use following scripts

SELECT RIGHT('000000'+ CONVERT(VARCHAR,Code),6) AS EmployeeCode FROM Employee;