Most efficient T-SQL way to pad a varchar on the l

问题:

As compared to say:

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

回答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.

回答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.

回答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.

回答4:

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

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

回答5:

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

回答6:

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)

回答7:

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

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

回答9:

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

回答10:

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)

回答11:

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

回答12:

I hope this helps someone.

STUFF ( character_expression , start , length ,character_expression )

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

回答13:

How about this:

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

3 is the number of zeros to pad

回答14:

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.

回答15:

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

回答16:

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;

回答17:

Here is how I would normally pad a varchar

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