How to pad data with leading zeros in SQL
Mar 14, 2024
I needed my value to be 8 characters long; and if it wasn’t that, the first few should be “0” until there were 8 characters.
You can use the REPLICATE() and RIGHT() functions.
Here’s the SQL:
SELECT
employeeIdAsText,
RIGHT(REPLICATE('0', 8) + employeeIdAsText, 8) AS employeeIdPadded
FROM dbo.Employees
This only works if the field is text (ex VARCHAR) type.
If your text is numeric, you’ll have to CAST it first:
SELECT
employeeId,
RIGHT(REPLICATE('0', 8) + CAST(employeeId AS VARCHAR(8)), 8) AS employeeIdPadded
FROM dbo.Employees