Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

If the field is already a string, this will work

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

If you want nulls to show as '000'

It might be an integer -- then you would want

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. eg '0000' and VARCHAR(4)),4

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

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.

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)