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

Question

As compared to say: REPLICATE(@padchar, @len - LEN(@str)) + @str

Answer 1

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

perhaps something like

right('XXXXXXXXXXXX'+ @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.

Answer 2

There is no more efficient way than that.
Really, it's not too bad - you're able to tell it exactly how much space to add.

Answer 3

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)

Answer 4

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

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

Answer 5

Ew I wouldn't dirty my data like that. Perform the string manipulation in code instead.

Answer 6

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

Answer 7

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

Answer 8

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, '_')

Answer 9

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.

Answer 10

How about this:

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

3 is the number of zeros to pad

Answer 11

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