To pad an integer with leading zeros to a specific length To display the integer as a decimal value, call its ToString(String) method, and pass the string "Dn" as the value of the format parameter, where n represents the minimum length of the string.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With