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

Solution 1:

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.

Solution 2:

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.

Solution 3:

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.

Solution 4:

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

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