Il modo T-SQL più efficiente per riempire un varchar sulla sinistra con una certa lunghezza?



varchar sql (14)

So che questo è stato inizialmente chiesto nel 2008, ma ci sono alcune nuove funzioni introdotte con SQL Server 2012. La funzione FORMAT semplifica il riempimento a sinistra con zeri. Effettuerà anche la conversione per te:

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

Aggiornare:

Volevo testare personalmente l'effettiva efficienza della funzione FORMAT. Sono rimasto piuttosto sorpreso nel constatare che l'efficienza non era molto buona rispetto alla risposta originale di . Anche se trovo la funzione FORMAT più pulita, non è molto efficiente in termini di tempo di esecuzione. La tabella Tally che ho usato ha 64.000 record. Complimenti a Martin Smith per aver sottolineato l'efficienza del tempo di esecuzione.

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

Tempi di esecuzione di SQL Server: tempo CPU = 2157 ms, tempo trascorso = 2696 ms.

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

Tempi di esecuzione di SQL Server:

Tempo CPU = 31 ms, tempo trascorso = 235 ms.

Rispetto a dire:

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

Forse un colpo secco ho queste UDF da battere a sinistra ea destra

ALTER   Function [dbo].[fsPadLeft](@var varchar(200),@padChar char(1)='0',@len int)
returns varchar(300)
as
Begin

return replicate(@PadChar,@len-Len(@var))[email protected]

end

e a destra

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

Ecco come vorrei normalmente caricare un varchar

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

Non sono sicuro che il metodo che fornisci sia davvero inefficiente, ma un modo alternativo, a patto che non debba essere flessibile nella lunghezza o nel carattere di riempimento, sarebbe (presumendo che tu lo voglia riempire con " Da 0 "a 10 caratteri:

DECLARE
   @pad_characters VARCHAR(10)

SET @pad_characters = '0000000000'

SELECT RIGHT(@pad_characters + @str, 10)

Io uso questo. Ti permette di determinare la lunghezza che vuoi che sia il risultato e un carattere di riempimento predefinito, se non ne viene fornito uno. Ovviamente è possibile personalizzare la lunghezza dell'input e dell'output per i massimi in cui si sta eseguendo.

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

Il tuo chilometraggio può variare. :-)

Joey Morgan
Programmatore / Analista Principal I.
Business Unit WellPoint Medicaid


Per fornire valori numerici arrotondati a due cifre decimali ma riempiti a destra con zeri, se necessario, ho:

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)

Se qualcuno può pensare a un modo più ordinato, sarebbe apprezzato - quanto sopra sembra maldestro .

Nota : in questo caso, sto utilizzando SQL Server per inviare i report via email in formato HTML e quindi desidero formattare le informazioni senza coinvolgere uno strumento aggiuntivo per analizzare i dati.


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

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

Ecco la mia soluzione, che evita le stringhe troncate e utilizza semplicemente l'SQL. Grazie a @AlexCuse , @Kevin e @Sklivvz , le cui soluzioni sono alla base di questo codice.

 --[@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][email protected], [@maxLengthOfPaddedString][email protected];

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

probabilmente eccessivo, uso spesso questa 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

In modo che tu possa fare cose come:

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

In SQL Server 2005 e versioni successive è possibile creare una funzione CLR per eseguire questa operazione.


Mi è piaciuta la soluzione vnRocks, qui è sotto forma di 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

Cosa ne pensi di questo:

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

3 è il numero di zeros da riempire


Diverse persone hanno dato versioni di questo:

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

fai attenzione perché troncerà i tuoi dati effettivi se è più lungo di n.


questo è un modo semplice per andare a sinistra:

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

Dove x è il numero del pad e y è il carattere del pad.

campione:

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




tsql