用前导零填充字符串，因此在SQL Server 2008中为3个字符长

本文翻译自：Pad a string with leading zeros so it's 3 characters long in SQL Server 2008

I have a string that is up to 3 characters long when it's first created in SQL Server 2008 R2. 在SQL Server 2008 R2中首次创建时，该字符串的长度最多为3个字符。

I would like to pad it with leading zeros, so if its original value was '1' then the new value would be '001'. 我想用前导零填充它，因此，如果其原始值是“ 1”，那么新值将是“ 001”。 Or if its original value was '23' the new value is '023'. 或者，如果其原始值为'23'，则新值为'023'。 Or if its original value is '124' then new value is the same as original value. 或者，如果其原始值为“ 124”，则新值与原始值相同。

I am using SQL Server 2008 R2. 我正在使用SQL Server 2008 R2。 How would I do this using T-SQL? 我将如何使用T-SQL做到这一点？

---

#1楼

参考：https://stackoom.com/question/18KH6/用前导零填充字符串-因此在SQL-Server-中为-个字符长

---

#2楼

If the field is already a string, this will work 如果该字段已经是字符串，则可以使用

 SELECT RIGHT('000'+ISNULL(field,''),3)

If you want nulls to show as '000' 如果您希望null显示为'000'

It might be an integer -- then you would want 它可能是整数-那么您需要

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

As required by the question this answer only works if the length <= 3, if you want something larger you need to change the string constant and the two integer constants to the width needed. 根据问题的要求，此答案仅在长度<= 3时有效，如果要更大，则需要将字符串常量和两个整数常量更改为所需的宽度。 eg '0000' and VARCHAR(4)),4 例如'0000' and VARCHAR(4)),4

---

#3楼

对于整数，可以使用从int到varchar的隐式转换：

SELECT RIGHT(1000 + field, 3)

---

#4楼

对于那些想要更新其现有数据的人，这里是查询：

update SomeEventTable set eventTime=RIGHT('00000'+ISNULL(eventTime, ''),5)

---

#5楼

Here's a more general technique for left-padding to any desired width: 这是一种用于将左侧填充到任何所需宽度的更通用的技术：

declare @x     int     = 123 -- value to be padded
declare @width int     = 25  -- desired width
declare @pad   char(1) = '0' -- pad character

select right_justified = replicate(
                           @pad ,
                           @width-len(convert(varchar(100),@x))
                           )
                       + convert(varchar(100),@x)

However, if you're dealing with negative values, and padding with leading zeroes, neither this, nor other suggested technique will work. 但是，如果要处理负值，并用前导零填充，则此方法或其他建议的方法都将无效。 You'll get something that looks like this: 您将获得如下所示的内容：

00-123

[Probably not what you wanted] [可能不是您想要的]

So … you'll have to jump through some additional hoops Here's one approach that will properly format negative numbers: 所以……您将不得不跳过一些额外的步骤，这是一种可以正确设置负数格式的方法：

declare @x     float   = -1.234
declare @width int     = 20
declare @pad   char(1) = '0'

select right_justified = stuff(
         convert(varchar(99),@x) ,                            -- source string (converted from numeric value)
         case when @x < 0 then 2 else 1 end ,                 -- insert position
         0 ,                                                  -- count of characters to remove from source string
         replicate(@pad,@width-len(convert(varchar(99),@x)) ) -- text to be inserted
         )

One should note that the convert() calls should specify an [n]varchar of sufficient length to hold the converted result with truncation. 应该注意的是， convert()调用应指定一个[n]varchar ，其长度应足以截断转换后的结果。

---

#6楼

我一直发现以下方法非常有帮助。

REPLICATE('0', 5 - LEN(Job.Number)) + CAST(Job.Number AS varchar) as 'NumberFull'