A forma mais eficiente de colocar um varchar à esquerda a um certo comprimento?
em comparação com dizer:
REPLICATE(@padchar, @len - LEN(@str)) + @str
17 answers
right('XXXXXXXXXXXX'+ rtrim(@str), @n)
Onde o X é o seu carácter de preenchimento e o @n é o número de caracteres na sequência resultante (assumindo que precisa do preenchimento porque está a lidar com um comprimento fixo).
Mas como eu disse, devias evitar fazer isto na tua base de dados.Eu sei que isto foi originalmente perguntado em 2008, mas existem algumas novas funções que foram introduzidas com o SQL Server 2012. A função do formato simplifica o preenchimento à esquerda com zeros. Ele também irá realizar a conversão para você:
declare @n as int = 2
select FORMAT(@n, 'd10') as padWithZeros
Actualizar:
Eu queria testar a eficiência real da função de formato. Fiquei bastante surpreendido ao ver que a eficiência não era muito boa em comparação com a resposta original do AlexCuse. Embora eu ache o formato de função mais limpo, não é muito eficiente em termos de tempo de execução. A tabela de registo que usei tem 64 mil registos. Parabéns aMartin Smith por apontar a eficiência do tempo de execução.
SET STATISTICS TIME ON
select FORMAT(N, 'd10') as padWithZeros from Tally
SET STATISTICS TIME OFF
Os tempos de execução do servidor SQL: Tempo de CPU = 2157 ms, tempo decorrido = 2696 ms.
SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF
Tempo de execução do servidor SQL:
Tempo da CPU = 31 ms, tempo decorrido = 235 ms
Várias pessoas deram versões disto:
right('XXXXXXXXXXXX'+ @str, @n)
Tenha cuidado com isso porque ele irá truncar os seus dados reais se for mais longo do que n.
@padstr = REPLICATE(@padchar, @len) -- this can be cached, done only once
SELECT RIGHT(@padstr + @str, @len)
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
E à 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
Não tenho a certeza de que o método que dás seja realmente ineficiente, mas uma forma alternativa, desde que não tenha de ser flexível no comprimento ou no carácter de enchimento, seria (assumindo que queres preenchê-lo com" 0 " a 10 caracteres:
DECLARE
@pad_characters VARCHAR(10)
SET @pad_characters = '0000000000'
SELECT RIGHT(@pad_characters + @str, 10)
No servidor SQL 2005 e mais tarde você pode criar uma função CLR para fazer isso.
Provavelmente exagero, uso frequentemente esta 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 possas fazer coisas como:
select dbo.f_pad_before('aaa', 10, '_')
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
REPLACE(STR(FACT_HEAD.FACT_NO, x, 0), ' ', y)
Onde x
é o número da casa e y
é o carácter da casa.
Amostra:
REPLACE(STR(FACT_HEAD.FACT_NO, 3, 0), ' ', 0)
select right(replicate(@padchar, @len) + @str, @len)
STUFF (character_ expression , start , length ,character_ Expression )
Seleccione o material (@str, 1, 0, replicado ('0', @n - len (@str)))
replace((space(3 - len(MyField))
3 é o número de zeros
to pad
Para fornecer valores numéricos arredondados às duas casas decimais, mas com uma almofada direita de zeros, se necessário, 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 consegue pensar em uma maneira melhor, isso seria apreciado-o acima parece desajeitado .
Nota : nesta instância, estou a usar o servidor SQL para enviar relatórios por e-mail em formato HTML e por isso desejo formatar a informação sem envolver uma ferramenta adicional para processar os dados.
/*===============================================================
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
A sua quilometragem pode variar. :-)Joey Morgan Programador / Analista Principal I
WellPoint Medicaid Business Unit ([3])
--[@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;
WHILE Len(@String) < 8
BEGIN
SELECT @String = '0' + @String
END