相比之下,说:
REPLICATE(@padchar, @len - LEN(@str)) + @str
相比之下,说:
REPLICATE(@padchar, @len - LEN(@str)) + @str
当前回答
下面是我的解决方案,它避免了截断字符串并使用普通的SQL。感谢@AlexCuse, @Kevin和@Sklivvz,他们的解决方案是这段代码的基础。
--[@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;
其他回答
可能有点夸张,我经常使用这个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
这样你就可以做这样的事情:
select dbo.f_pad_before('aaa', 10, '_')
我希望这能帮助到一些人。
STUFF ( character_expression , start , length ,character_expression )
select stuff(@str, 1, 0, replicate('0', @n - len(@str)))
这是我通常如何填充一个varchar
WHILE Len(@String) < 8
BEGIN
SELECT @String = '0' + @String
END
我知道这个问题最初是在2008年提出的,但是在SQL Server 2012中引入了一些新函数。FORMAT函数很好地简化了以零结尾的填充。它也会为你执行转换:
declare @n as int = 2
select FORMAT(@n, 'd10') as padWithZeros
更新:
我想亲自测试FORMAT函数的实际效率。我很惊讶地发现,与AlexCuse的原始答案相比,效率不是很好。虽然我发现FORMAT函数更简洁,但就执行时间而言,它不是很高效。我使用的tallytable有64,000条记录。Martin Smith指出了执行时间效率。
SET STATISTICS TIME ON
select FORMAT(N, 'd10') as padWithZeros from Tally
SET STATISTICS TIME OFF
SQL Server执行次数: CPU时间= 2157 ms,运行时间= 2696 ms。
SET STATISTICS TIME ON
select right('0000000000'+ rtrim(cast(N as varchar(5))), 10) from Tally
SET STATISTICS TIME OFF
SQL Server执行次数:
CPU时间= 31 ms,运行时间= 235 ms。
这个怎么样:
replace((space(3 - len(MyField))
3是要填充的零的个数