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

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.

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)

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)

probably overkill, I often use this UDF:

CREATE FUNCTION [dbo].[f_pad_before](@string VARCHAR(255), @desired_length INTEGER, @pad_character CHAR(1))
RETURNS VARCHAR(255) AS  
BEGIN

-- Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character
 RETURN ltrim(rtrim(
        CASE
          WHEN LEN(@string) < @desired_length
            THEN REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character) + @string
          ELSE @string
        END
        ))
END

So that you can do things like:

select dbo.f_pad_before('aaa', 10, '_')

select right(replicate(@padchar, @len) + @str, @len)

In SQL Server 2005 and later you could create a CLR function to do this.

How about this:

replace((space(3 - len(MyField))

3 is the number of zeros to pad

I know this isn't adding much to the conversation at this point but I'm running a file generation procedure and its going incredibly slow. I've been using replicate and saw this trim method and figured I'd give it a shot.

You can see in my code where the switch between the two is in addition to the new @padding variable (and the limitation that now exists). I ran my procedure with the function in both states with the same results in execution time. So at least in SQLServer2016, I'm not seeing any difference in efficiency that other found.

Anyways, here's my UDF that I wrote years ago plus the changes today which is much the same as other's other than it has a LEFT/RIGHT param option and some error checking.

CREATE FUNCTION PadStringTrim 
(
    @inputStr varchar(500), 
    @finalLength int, 
    @padChar varchar (1),
    @padSide varchar(1)
)
RETURNS VARCHAR(500)

AS BEGIN
    -- the point of this function is to avoid using replicate which is extremely slow in SQL Server
    -- to get away from this though we now have a limitation of how much padding we can add, so I've settled on a hundred character pad 
    DECLARE @padding VARCHAR (100) = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
    SET @padding = REPLACE(@padding, 'X', @padChar)


    SET @inputStr = RTRIM(LTRIM(@inputStr))

    IF LEN(@inputStr) > @finalLength 
        RETURN '!ERROR!' -- can search for ! in the returned text 

    ELSE IF(@finalLength > LEN(@inputStr))
        IF @padSide = 'L'
            SET @inputStr = RIGHT(@padding + @inputStr, @finalLength)
            --SET @inputStr = REPLICATE(@padChar, @finalLength - LEN(@inputStr)) + @inputStr
        ELSE IF @padSide = 'R'
            SET @inputStr = LEFT(@inputStr + @padding, @finalLength)
            --SET @inputStr = @inputStr + REPLICATE(@padChar, @finalLength - LEN(@inputStr)) 



    -- if LEN(@inputStr) = @finalLength we just return it 
    RETURN @inputStr;
END

-- SELECT  dbo.PadStringTrim( tblAccounts.account, 20, '~' , 'R' ) from tblAccounts
-- SELECT  dbo.PadStringTrim( tblAccounts.account, 20, '~' , 'L' ) from tblAccounts

I have one function that lpad with x decimals: CREATE FUNCTION [dbo].[LPAD_DEC] ( -- Add the parameters for the function here @pad nvarchar(MAX), @string nvarchar(MAX), @length int, @dec int ) RETURNS nvarchar(max) AS BEGIN -- Declare the return variable here DECLARE @resp nvarchar(max)

IF LEN(@string)=@length
BEGIN
    IF CHARINDEX('.',@string)>0
    BEGIN
        SELECT @resp = CASE SIGN(@string)
            WHEN -1 THEN
                -- Nros negativos grandes con decimales
                concat('-',SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            ELSE
                -- Nros positivos grandes con decimales
                concat(SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(@string,@length,@dec)))                  
            END
    END
    ELSE
    BEGIN
        SELECT @resp = CASE SIGN(@string)
            WHEN -1 THEN
                --Nros negativo grande sin decimales
                concat('-',SUBSTRING(replicate(@pad,@length),1,(@length-3)-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            ELSE
                -- Nros positivos grandes con decimales
                concat(SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(@string,@length,@dec)))                  
            END                     
    END
END
ELSE
    IF CHARINDEX('.',@string)>0
    BEGIN
        SELECT @resp =CASE SIGN(@string)
            WHEN -1 THEN
                -- Nros negativos con decimales
                concat('-',SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            ELSE
                --Ntos positivos con decimales
                concat(SUBSTRING(replicate(@pad,@length),1,@length-len(@string)),ltrim(str(abs(@string),@length,@dec))) 
            END
    END
    ELSE
    BEGIN
        SELECT @resp = CASE SIGN(@string)
            WHEN -1 THEN
                -- Nros Negativos sin decimales
                concat('-',SUBSTRING(replicate(@pad,@length-3),1,(@length-3)-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            ELSE
                -- Nros Positivos sin decimales
                concat(SUBSTRING(replicate(@pad,@length),1,(@length-3)-len(@string)),ltrim(str(abs(@string),@length,@dec)))
            END
    END
RETURN @resp

END

Here is my solution. I can pad any character and it is fast. Went with simplicity. You can change variable size to meet your needs.

Updated with a parameter to handle what to return if null: null will return a null if null

CREATE OR ALTER FUNCTION code.fnConvert_PadLeft(
    @in_str nvarchar(1024),
    @pad_length int, 
    @pad_char nchar(1) = ' ', 
    @rtn_null NVARCHAR(1024) = '')
RETURNS NVARCHAR(1024)
AS
BEGIN
     DECLARE @rtn  NCHAR(1024) = ' '
     RETURN RIGHT(REPLACE(@rtn,' ',@pad_char)+ISNULL(@in_str,@rtn_null), @pad_length)
END
GO

CREATE OR ALTER FUNCTION code.fnConvert_PadRight(
    @in_str nvarchar(1024), 
    @pad_length int, 
    @pad_char nchar(1) = ' ', 
    @rtn_null NVARCHAR(1024) = '')
RETURNS NVARCHAR(1024)
AS
BEGIN
     DECLARE @rtn  NCHAR(1024) = ' '
     RETURN LEFT(ISNULL(@in_str,@rtn_null)+REPLACE(@rtn,' ',@pad_char), @pad_length)
END
GO 

-- Example
SET STATISTICS time ON 
SELECT code.fnConvert_PadLeft('88',10,'0',''), 
    code.fnConvert_PadLeft(null,10,'0',''), 
    code.fnConvert_PadLeft(null,10,'0',null), 
    code.fnConvert_PadRight('88',10,'0',''), 
    code.fnConvert_PadRight(null,10,'0',''),
    code.fnConvert_PadRight(null,10,'0',NULL)


0000000088  0000000000  NULL    8800000000  0000000000  NULL

To provide numerical values rounded to two decimal places but right-padded with zeros if required I have:

DECLARE @value = 20.1
SET @value = ROUND(@value,2) * 100
PRINT LEFT(CAST(@value AS VARCHAR(20)), LEN(@value)-2) + '.' + RIGHT(CAST(@value AS VARCHAR(20)),2)

If anyone can think of a neater way, that would be appreciated - the above seems clumsy.

Note: in this instance, I'm using SQL Server to email reports in HTML format and so wish to format the information without involving an additional tool to parse the data.