将左侧的 varchar 填充到特定长度的最有效 T-SQL 方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/121864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Most efficient T-SQL way to pad a varchar on the left to a certain length?
提问by Cade Roux
As compared to say:
相比之下说：
REPLICATE(@padchar, @len - LEN(@str)) + @str
回答by AlexCuse
This is simply an inefficient use of SQL, no matter how you do it.
无论您如何操作，这都只是对 SQL 的低效使用。
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).
其中 X 是您的填充字符，@n 是结果字符串中的字符数（假设您需要填充，因为您处理的是固定长度）。
But as I said you should really avoid doing this in your database.
但正如我所说，你真的应该避免在你的数据库中这样做。
回答by jediCouncilor
I know this was originally asked back in 2008, but there are some new functions that were introduced with SQL Server 2012. The FORMAT functionsimplifies padding left with zeros nicely. It will also perform the conversion for you:
我知道这最初是在 2008 年提出的，但 SQL Server 2012 引入了一些新函数。FORMAT 函数很好地简化了用零填充的左边距。它还将为您执行转换：
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 Smithfor pointing out execution time efficiency.
我想自己测试一下 FORMAT 函数的实际效率。我很惊讶地发现与AlexCuse的原始答案相比，效率不是很好。虽然我发现 FORMAT 函数更简洁，但它在执行时间方面并不是很有效。我使用的 Tally 表有 64,000 条记录。荣誉对马丁·史密斯的指出了执行时间效率。
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.
SQL Server 执行时间：CPU 时间 = 2157 毫秒，已用时间 = 2696 毫秒。
SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF
SQL Server Execution Times:
SQL Server 执行时间：
CPU time = 31 ms, elapsed time = 235 ms.
CPU 时间 = 31 毫秒，经过时间 = 235 毫秒。
回答by Kevin
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.
小心这一点，因为如果它长于 n，它会截断您的实际数据。
回答by Sklivvz
@padstr = REPLICATE(@padchar, @len) -- this can be cached, done only once
SELECT RIGHT(@padstr + @str, @len)
回答by TonyP
Perhaps an over kill I have these UDFs to pad left and right
也许是过度杀戮我有这些 UDF 可以左右填充
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
回答by Tom H
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:
我不确定您提供的方法是否真的效率低下，但另一种方法是（假设您想用“ 0" 到 10 个字符：
DECLARE
@pad_characters VARCHAR(10)
SET @pad_characters = '0000000000'
SELECT RIGHT(@pad_characters + @str, 10)
回答by ila
probably overkill, I often use this UDF:
可能矫枉过正，我经常使用这个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, '_')
回答by Ahmad
this is a simple way to pad left:
这是一种向左填充的简单方法：
REPLACE(STR(FACT_HEAD.FACT_NO, x, 0), ' ', y)
Where xis the pad number and yis the pad character.
哪里x是焊盘号，y是焊盘字符。
sample:
样本：
REPLACE(STR(FACT_HEAD.FACT_NO, 3, 0), ' ', 0)
回答by Kevin
I liked vnRocks solution, here it is in the form of a udf
我喜欢 vnRocks 解决方案，这里是 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
回答by vnRock
I hope this helps someone.
我希望这可以帮助别人。
STUFF ( character_expression , start , length ,character_expression )
select stuff(@str, 1, 0, replicate('0', @n - len(@str)))
