mid - sql substring()




A maneira mais eficiente do T-SQL para preencher um varchar à esquerda até um certo tamanho? (12)

Em comparação a dizer:

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

Aqui está como eu normalmente preencheria um varchar

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

Aqui está minha solução, que evita strings truncadas e usa SQL simples. Graças a @AlexCuse , @Kevin e @Sklivvz , cujas soluções são a base deste 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][email protected]longestStringValue, [@maxLengthOfPaddedString][email protected]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;

Eu gostei da solução vnRocks, aqui está na forma de um 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

Eu não tenho certeza que o método que você dá é realmente ineficiente, mas uma forma alternativa, desde que não precise ser flexível no comprimento ou caractere de preenchimento, seria (supondo que você deseja preenchê-lo com " 0 "a 10 caracteres:

DECLARE
   @pad_characters VARCHAR(10)

SET @pad_characters = '0000000000'

SELECT RIGHT(@pad_characters + @str, 10)

Eu sei que isso não está adicionando muito à conversa neste momento, mas eu estou executando um processo de geração de arquivos e está indo incrivelmente lento. Eu tenho usado o replicado e vi esse método de acabamento e imaginei que iria tentar.

Você pode ver no meu código onde o switch entre os dois é adicional à nova variável @padding (e a limitação que agora existe). Eu corri meu procedimento com a função em ambos os estados com os mesmos resultados em tempo de execução. Então, pelo menos no SQLServer2016, não vejo nenhuma diferença na eficiência que o outro encontrou.

De qualquer forma, aqui está a minha UDF que escrevi anos atrás, mais as alterações hoje, que é a mesma que a outra, além de ter uma opção de parâmetro LEFT / RIGHT e alguma verificação de erro.

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

Eu uso este. Ele permite que você determine o tamanho que deseja que o resultado seja, além de um caractere de preenchimento padrão, se um não for fornecido. É claro que você pode personalizar o tamanho da entrada e da saída para os valores máximos em que você está correndo.

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

Sua milhagem pode variar. :-)

Joey Morgan
Programador / Analista Principal I
Unidade de Negócios WellPoint Medicaid


No SQL Server 2005 e posterior, você poderia criar uma função CLR para fazer isso.


Para fornecer valores numéricos arredondados para duas casas decimais, mas preenchidos à direita com zeros, se necessário, eu tenho:

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 alguém puder pensar de uma forma mais simples, isso será apreciado - o que foi dito acima parece desajeitado .

Nota : neste exemplo, estou usando o SQL Server para enviar relatórios por e-mail em formato HTML e, portanto, desejo formatar as informações sem envolver uma ferramenta adicional para analisar os dados.


Talvez um over kill eu tenho esses UDFs para pad esquerda e direita

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]var

end

e para a direita

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

Várias pessoas deram versões disso:

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

tenha cuidado com isso porque truncará seus dados reais se ele for maior que n.


provavelmente exagero, eu geralmente uso esse 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

Então você pode fazer coisas como:

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

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




tsql