这是我的解决方案。我可以填充任何字符，它是快速的。选择简单。您可以更改可变大小以满足您的需要。

更新了一个参数来处理如果为空返回什么:null如果为空将返回null

CREATE OR ALTER FUNCTION code.fnConvert_PadLeft(
    @in_str nvarchar(1024),
    @pad_length int, 
    @pad_char nchar(1) = ' ', 
    @rtn_null NVARCHAR(1024) = '')
RETURNS NVARCHAR(1024)
AS
BEGIN
     DECLARE @rtn  NCHAR(1024) = ' '
     RETURN RIGHT(REPLACE(@rtn,' ',@pad_char)+ISNULL(@in_str,@rtn_null), @pad_length)
END
GO

CREATE OR ALTER FUNCTION code.fnConvert_PadRight(
    @in_str nvarchar(1024), 
    @pad_length int, 
    @pad_char nchar(1) = ' ', 
    @rtn_null NVARCHAR(1024) = '')
RETURNS NVARCHAR(1024)
AS
BEGIN
     DECLARE @rtn  NCHAR(1024) = ' '
     RETURN LEFT(ISNULL(@in_str,@rtn_null)+REPLACE(@rtn,' ',@pad_char), @pad_length)
END
GO 

-- Example
SET STATISTICS time ON 
SELECT code.fnConvert_PadLeft('88',10,'0',''), 
    code.fnConvert_PadLeft(null,10,'0',''), 
    code.fnConvert_PadLeft(null,10,'0',null), 
    code.fnConvert_PadRight('88',10,'0',''), 
    code.fnConvert_PadRight(null,10,'0',''),
    code.fnConvert_PadRight(null,10,'0',NULL)


0000000088  0000000000  NULL    8800000000  0000000000  NULL

要提供四舍五入到小数点后两位，但如果需要，右补零，我有:

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)

如果有人能想出一种更简洁的方法，那将是非常感激的——上面的方法似乎很笨拙。

注意:在本例中，我使用SQL Server以HTML格式发送电子邮件报告，因此希望格式化信息，而不涉及额外的工具来解析数据。

这是一个简单的左填充方法:

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

其中x是填充号y是填充字符。

示例:

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

下面是我的解决方案，它避免了截断字符串并使用普通的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;

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

可能有点夸张，我经常使用这个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, '_')