SQL Server Zero Pad Left

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
		(@TextToPad 	 	VARCHAR(8000),
		 @CharacterToPad	VARCHAR(1),
		 @NumberToPad	 	INT)
RETURNS		VARCHAR(8000)
AS
BEGIN
	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)
END