In a recent project, I had the task of padding a number with zeros. SQL Server doesn’t have a built in Zero Pad Left function. SQL Server does have a Right function. You can use this to create a zero padded left string.
SELECT RIGHT('0000000000' + @TextToZeroPad, 10) AS ZeroPaddedText
In the code above, I am take 10 zeros, appending a text value to it, then taking the right 10 characters. The end result is the same as padding the left with zeros.
SQL Server Zero Pad Left Function
You can take this a step further and create a scalar function out of it.
CREATE FUNCTION PadLeft
DECLARE @OutputText VARCHAR(8000)
-- Create a string with the maximum number of padded characters followed
-- by the text that we want to pad
SET @OutputText = REPLICATE(@CharacterToPad, @NumberToPad) + @TextToPad
-- Take the right X characters to simulate the padding
RETURN RIGHT(@OutputText, @NumberToPad)
2 thoughts on “SQL Server Zero Pad Left”
Sql Server has since version 2012 the Format() option
ie: SELECT FORMAT(id, ‘0000’) as myId FROM myTable;
if id = 1 you will get 0001 returned
Thank you. It worked