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

As compared to say:

REPLICATE(@padchar, @len - LEN(@str)) + @str

I hope this helps someone.

STUFF ( character_expression , start , length ,character_expression )

select stuff(@str, 1, 0, replicate('0', @n - len(@str)))

I liked vnRocks solution, here it is in the form of a udf

create function PadLeft(
      @String varchar(8000)
     ,@NumChars int
     ,@PadChar char(1) = ' ')
returns varchar(8000)
as
begin
    return stuff(@String, 1, 0, replicate(@PadChar, @NumChars - len(@String)))
end

I use this one. It allows you to determine the length you want the result to be as well as a default padding character if one is not provided. Of course you can customize the length of the input and output for whatever maximums you are running into.

/*===============================================================
 Author         : Joey Morgan
 Create date    : November 1, 2012
 Description    : Pads the string @MyStr with the character in 
                : @PadChar so all results have the same length
 ================================================================*/
 CREATE FUNCTION [dbo].[svfn_AMS_PAD_STRING]
        (
         @MyStr VARCHAR(25),
         @LENGTH INT,
         @PadChar CHAR(1) = NULL
        )
RETURNS VARCHAR(25)
 AS 
      BEGIN
        SET @PadChar = ISNULL(@PadChar, '0');
        DECLARE @Result VARCHAR(25);
        SELECT
            @Result = RIGHT(SUBSTRING(REPLICATE('0', @LENGTH), 1,
                                      (@LENGTH + 1) - LEN(RTRIM(@MyStr)))
                            + RTRIM(@MyStr), @LENGTH)

        RETURN @Result

      END

Your mileage may vary. :-)

Joey Morgan
Programmer/Analyst Principal I
WellPoint Medicaid Business Unit

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

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

How about this:

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

3 is the number of zeros to pad

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.

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)

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

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

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.

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

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

Here's my solution, which avoids truncated strings and uses plain ol' SQL. Thanks to @AlexCuse, @Kevin and @Sklivvz, whose solutions are the foundation of this code.

 --[@charToPadStringWith] is the character you want to pad the string with.
declare @charToPadStringWith char(1) = 'X';

-- Generate a table of values to test with.
declare @stringValues table (RowId int IDENTITY(1,1) NOT NULL PRIMARY KEY, StringValue varchar(max) NULL);
insert into @stringValues (StringValue) values (null), (''), ('_'), ('A'), ('ABCDE'), ('1234567890');

-- Generate a table to store testing results in.
declare @testingResults table (RowId int IDENTITY(1,1) NOT NULL PRIMARY KEY, StringValue varchar(max) NULL, PaddedStringValue varchar(max) NULL);

-- Get the length of the longest string, then pad all strings based on that length.
declare @maxLengthOfPaddedString int = (select MAX(LEN(StringValue)) from @stringValues);
declare @longestStringValue varchar(max) = (select top(1) StringValue from @stringValues where LEN(StringValue) = @maxLengthOfPaddedString);
select [@longestStringValue]=@longestStringValue, [@maxLengthOfPaddedString]=@maxLengthOfPaddedString;

-- Loop through each of the test string values, apply padding to it, and store the results in [@testingResults].
while (1=1)
begin
    declare
        @stringValueRowId int,
        @stringValue varchar(max);

    -- Get the next row in the [@stringLengths] table.
    select top(1) @stringValueRowId = RowId, @stringValue = StringValue
    from @stringValues 
    where RowId > isnull(@stringValueRowId, 0) 
    order by RowId;

    if (@@ROWCOUNT = 0) 
        break;

    -- Here is where the padding magic happens.
    declare @paddedStringValue varchar(max) = RIGHT(REPLICATE(@charToPadStringWith, @maxLengthOfPaddedString) + @stringValue, @maxLengthOfPaddedString);

    -- Added to the list of results.
    insert into @testingResults (StringValue, PaddedStringValue) values (@stringValue, @paddedStringValue);
end

-- Get all of the testing results.
select * from @testingResults;

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.

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

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

this is a simple way to pad left:

REPLACE(STR(FACT_HEAD.FACT_NO, x, 0), ' ', y)

Where x is the pad number and y is the pad character.

sample:

REPLACE(STR(FACT_HEAD.FACT_NO, 3, 0), ' ', 0)

Here is how I would normally pad a varchar

WHILE Len(@String) < 8
BEGIN
    SELECT @String = '0' + @String
END