Pad a string with leading zeros so it's 3 char

Question

I have a string that is up to 3 characters long when it's first created in SQL Server 2008 R2.

I would like to pad it with leading zeros, so if its original value was '1' then the new value would be '001'. Or if its original value was '23' the new value is '023'. Or if its original value is '124' then new value is the same as original value.

I am using SQL Server 2008 R2. How would I do this using T-SQL?

Answer 1

Wrote this because I had requirements for up to a specific length (9). Pads the left with the @pattern ONLY when the input needs padding. Should always return length defined in @pattern.

declare @charInput as char(50) = 'input'

--always handle NULL :)
set @charInput = isnull(@charInput,'')

declare @actualLength as int = len(@charInput)

declare @pattern as char(50) = '123456789'
declare @prefLength as int = len(@pattern)

if @prefLength > @actualLength
    select Left(Left(@pattern, @prefLength-@actualLength) + @charInput, @prefLength)
else
    select @charInput

Returns 1234input

Answer 2

The safe method:

SELECT REPLACE(STR(n,3),' ','0')

This has the advantage of returning the string '***' for n < 0 or n > 999, which is a nice and obvious indicator of out-of-bounds input. The other methods listed here will fail silently by truncating the input to a 3-character substring.

Answer 3

Although the question was for SQL Server 2008 R2, in case someone is reading this with version 2012 and above, since then it became much easier by the use of FORMAT.

You can either pass a standard numeric format string or a custom numeric format string as the format argument (thank Vadim Ovchinnikov for this hint).

For this question for example a code like

DECLARE @myInt INT = 1;
-- One way using a standard numeric format string
PRINT FORMAT(@myInt,'D3');
-- Other way using a custom numeric format string
PRINT FORMAT(@myInt,'00#');

outputs

001
001

Answer 4

Here is a variant of Hogan's answer which I use in SQL Server Express 2012:

SELECT RIGHT(CONCAT('000', field), 3)

Instead of worrying if the field is a string or not, I just CONCAT it, since it'll output a string anyway. Additionally if the field can be a NULL, using ISNULL might be required to avoid function getting NULL results.

SELECT RIGHT(CONCAT('000', ISNULL(field,'')), 3)

Answer 5

For integers you can use implicit conversion from int to varchar:

SELECT RIGHT(1000 + field, 3)

Answer 6

Use this function which suits every situation.

CREATE FUNCTION dbo.fnNumPadLeft (@input INT, @pad tinyint)
RETURNS VARCHAR(250)
AS BEGIN
    DECLARE @NumStr VARCHAR(250)

    SET @NumStr = LTRIM(@input)

    IF(@pad > LEN(@NumStr))
        SET @NumStr = REPLICATE('0', @Pad - LEN(@NumStr)) + @NumStr;

    RETURN @NumStr;
END

Sample output

SELECT [dbo].[fnNumPadLeft] (2016,10) -- returns 0000002016
SELECT [dbo].[fnNumPadLeft] (2016,5) -- returns 02016
SELECT [dbo].[fnNumPadLeft] (2016,2) -- returns 2016
SELECT [dbo].[fnNumPadLeft] (2016,0) -- returns 2016