Most efficient T-SQL way to pad a varchar on the left to a certain length?

Question

This is simply an inefficient use of SQL, no matter how you do it.

perhaps something like

right('XXXXXXXXXXXX'+ rtrim(@str), @n)

where X is your padding character and @n is the number of characters in the resulting string (assuming you need the padding because you are dealing with a fixed length).

But as I said you should really avoid doing this in your database.

I know this was originally asked back in 2008, but there are some new functions that were introduced with SQL Server 2012. The FORMAT function simplifies padding left with zeros nicely. It will also perform the conversion for you:

declare @n as int = 2
select FORMAT(@n, 'd10') as padWithZeros

Update:

I wanted to test the actual efficiency of the FORMAT function myself. I was quite surprised to find the efficiency was not very good compared to the original answer from AlexCuse. Although I find the FORMAT function cleaner, it is not very efficient in terms of execution time. The Tally table I used has 64,000 records. Kudos to Martin Smith for pointing out execution time efficiency.

SET STATISTICS TIME ON
select FORMAT(N, 'd10') as padWithZeros from Tally
SET STATISTICS TIME OFF

SQL Server Execution Times: CPU time = 2157 ms, elapsed time = 2696 ms.

SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF

SQL Server Execution Times:

CPU time = 31 ms, elapsed time = 235 ms.

Several people gave versions of this:

right('XXXXXXXXXXXX'+ @str, @n)

be careful with that because it will truncate your actual data if it is longer than n.

@padstr = REPLICATE(@padchar, @len) -- this can be cached, done only once

SELECT RIGHT(@padstr + @str, @len)

Perhaps an over kill I have these UDFs to pad left and right

ALTER   Function [dbo].[fsPadLeft](@var varchar(200),@padChar char(1)='0',@len int)
returns varchar(300)
as
Begin

return replicate(@PadChar,@len-Len(@var))+@var

end

and to right

ALTER function [dbo].[fsPadRight](@var varchar(200),@padchar char(1)='0', @len int) returns varchar(201) as
Begin

--select @padChar=' ',@len=200,@var='hello'


return  @var+replicate(@PadChar,@len-Len(@var))
end

I'm not sure that the method that you give is really inefficient, but an alternate way, as long as it doesn't have to be flexible in the length or padding character, would be (assuming that you want to pad it with "0" to 10 characters:

DECLARE
   @pad_characters VARCHAR(10)

SET @pad_characters = '0000000000'

SELECT RIGHT(@pad_characters + @str, 10)