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.
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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 |
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