La forma más eficiente de T-SQL para rellenar un varchar a la izquierda a una cierta longitud?

Sé que esto se preguntó originalmente en 2008, pero hay algunas funciones nuevas que se introdujeron con SQL Server 2012. La función de FORMATO simplifica muy bien el relleno dejado con ceros. También realizará la conversión para usted:

declare @n as int = 2
select FORMAT(@n, 'd10') as padWithZeros

Actualización:

Quería probar la eficiencia real de la función de FORMATO yo mismo. Me sorprendió bastante encontrar que la eficiencia no era muy buena en comparación con la respuesta original de AlexCuse. Aunque Me parece que el limpiador de funciones de formato, no es muy eficiente en términos de tiempo de ejecución. La tabla de cuentas que usé tiene 64.000 registros. Felicitaciones a Martin Smith por señalar la eficiencia del tiempo de ejecución.

SET STATISTICS TIME ON
select FORMAT(N, 'd10') as padWithZeros from Tally
SET STATISTICS TIME OFF

Tiempos de ejecución de SQL Server: Tiempo de CPU = 2157 ms, tiempo transcurrido = 2696 ms.

SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF

Tiempos de ejecución de SQL Server:

Tiempo de CPU = 31 ms, tiempo transcurrido = 235 ms.

Varias personas dieron versiones de esto:

right('XXXXXXXXXXXX'+ @str, @n)

Tenga cuidado con eso porque truncará sus datos reales si es más largo que n.

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

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

Tal vez una muerte excesiva Tengo estos UDF para rellenar izquierda y derecha

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

Y a la derecha

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

No estoy seguro de que el método que das sea realmente ineficiente, pero una forma alternativa, siempre y cuando no tenga que ser flexible en la longitud o el carácter de relleno, sería (suponiendo que quieras rellenarlo con "0" a 10 caracteres:

DECLARE
   @pad_characters VARCHAR(10)

SET @pad_characters = '0000000000'

SELECT RIGHT(@pad_characters + @str, 10)

En SQL Server 2005 y posteriores puede crear una función CLR para hacer esto.

Probablemente exagerado, a menudo uso este 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

Para que puedas hacer cosas como:

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

Me gustó la solución vnRocks, aquí está en forma de un 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

Esta es una forma sencilla de rellenar la izquierda:

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

Donde x es el número del pad y y es el carácter del pad.

Muestra:

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

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

Espero que esto ayude a alguien.

STUFF (character_expression , start , length ,character_expression)

Seleccionar cosas (@str, 1, 0, replicar ('0', @n - len (@str)))

¿qué tal esto:

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

3 es el número de zeros a pad

Para proporcionar valores numéricos redondeados a dos decimales pero rellenados a la derecha con ceros si es necesario, tengo:

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)

Si alguien puede pensar en una manera más ordenada, eso sería apreciado - lo anterior parece torpe.

Nota: en este caso, estoy usando SQL Server para enviar informes por correo electrónico en formato HTML y, por lo tanto, deseo formatear la información sin involucrar una herramienta adicional para analizar los datos.

Yo uso este. Le permite determinar la longitud que desea que tenga el resultado, así como un carácter de relleno predeterminado si no se proporciona uno. Por supuesto, puede personalizar la longitud de la entrada y la salida para los máximos que se encuentre.

/*===============================================================
 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

Su kilometraje puede variar. :-)

Joey Morgan
Programador / Analista Principal I
WellPoint Medicaid Business Unit

Aquí está mi solución, que evita cadenas truncadas y utiliza SQL simple. Gracias a @AlexCuse, @Kevin y @Sklivvz, cuyas soluciones son la base de este código.

 --[@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;

Así es como normalmente rellenaría un varchar

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