SQL SERVER – How To Prefix zeros to an Integer

There are some cases where we have to add zeros to an integer as prefix. This can be achieved  by the Right() string function.  Let us take a table – Employee, which is having an Identity column, EmpId with values 1,2,3 and so on.

Create Table EmpDetails(EmpId int identity(1,1), EmpName varchar(25), Salary Money)

INSERT INTO EmpDetails
SELECT 'AAA', 500000
UNION ALL
SELECT 'BBB', 400000
UNION ALL
SELECT 'CCC', 900000

Now on checking the data,

SELECT EMPID,EMPNAME ,SALARY FROM Empdetails

 

Now, In order to add zeros as prefix to the Empid,we will use  Right() string function as shown below.

SELECT RIGHT('00000'+CONVERT(VARCHAR,EMPID),5) EmpId, EMPNAME ,SALARY  FROM EmpDetails

Here we have converted EmpId to VARCHAR Data type,  concatenated zeros to the EmpId and applied Right() string function to the result set.

The Output is,