Formatting Numbers by padding with leading zeros in SQL Server

Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

Just use the FORMAT function (works on SQL Server 2012 or newer):

SELECT FORMAT(EmployeeID, '000000')
FROM dbo.RequestItems
WHERE ID=0 

Reference: http://msdn.microsoft.com/en-us/library/hh213505.aspx

You can change your procedure in this way

SELECT Right('000000' + CONVERT(NVARCHAR, EmployeeID), 6) AS EmpIDText, 
       EmployeeID
FROM dbo.RequestItems 
WHERE ID=0 

However this assumes that your EmployeeID is a numeric value and this code change the result to a string, I suggest to add again the original numeric value

EDIT Of course I have not read carefully the question above. It says that the field is a char(6) so EmployeeID is not a numeric value. While this answer has still a value per se, it is not the correct answer to the question above.

Hated having to CONVERT the int, and this seems much simpler. Might even perform better since there's only one string conversion and simple addition.

select RIGHT(1000000 + EmployeeId, 6) ...

Just make sure the "1000000" has at least as many zeros as the size needed.

I am posting all at one place, all works for me to pad with 4 leading zero :)

declare @number int =  1;
print right('0000' + cast(@number as varchar(4)) , 4)
print right('0000' + convert(varchar(4), @number) , 4)
print right(replicate('0',4) + convert(varchar(4), @number) , 4)
print  cast(replace(str(@number,4),' ','0')as char(4))
print format(@number,'0000')

From version 2012 and on you can use

SELECT FORMAT(EmployeeID,'000000')
FROM dbo.RequestItems
WHERE ID=0

Another way, just for completeness.

DECLARE @empNumber INT = 7123
SELECT STUFF('000000', 6-LEN(@empNumber)+1, LEN(@empNumber), @empNumber)

Or, as per your query

SELECT STUFF('000000', 6-LEN(EmployeeID)+1, LEN(EmployeeID), EmployeeID) 
         AS EmployeeCode
FROM dbo.RequestItems
WHERE ID=0

As clean as it could get and give scope of replacing with variables:

Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) from dbo.RequestItems
WHERE ID=0

To account for negative numbers without overflowing 6 characters...

FORMAT(EmployeeID, '000000;-00000')

SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID
FROM dbo.RequestItems 
WHERE ID=0

SELECT 
    cast(replace(str(EmployeeID,6),' ','0')as char(6)) 
FROM dbo.RequestItems
WHERE ID=0

The solution works for signed / negative numbers with leading zeros, for all Sql versions:

DECLARE
    @n money = -3,
    @length tinyint = 15,
    @decimals tinyint = 0

SELECT REPLICATE('-', CHARINDEX('-', @n, 1)) + REPLACE(REPLACE(str(@n, @length, @decimals), '-', ''), ' ', '0')

The simplest is always the best:

Select EmployeeID*1 as EmployeeID 

In my version of SQL I can't use REPLICATE. SO I did this:

SELECT 
    CONCAT(REPEAT('0', 6-LENGTH(emplyeeID)), emplyeeID) AS emplyeeID 
FROM 
    dbo.RequestItems`