如何在 T-SQL 中将 int 转换为零填充字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/309165/
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
How do I convert an int to a zero padded string in T-SQL?
提问by flipdoubt
Let's say I have an int with the value of 1. How can I convert that int to a zero padded string, such as 00000001?
假设我有一个值为 1 的 int。如何将该 int 转换为填充零的字符串，例如00000001？
采纳答案by Charles Bretana
Declare @MyInt integer Set @MyInt = 123
Declare @StrLen TinyInt Set @StrLen = 8
Select Replace(Str(@MyInt, @StrLen), ' ' , '0')
回答by Mitch Wheat
Another way is:
另一种方式是：
DECLARE @iVal int = 1
select REPLACE(STR(@iVal, 8, 0), ' ', '0')
回答by gordy
as of SQL Server 2012 you can now do this:
从 SQL Server 2012 开始，您现在可以执行以下操作：
format(@int, '0000#')
回答by RicardoBalda
This work for me:
这对我有用：
SELECT RIGHT('000' + CAST(Table.Field AS VARCHAR(3)),3) FROM Table
...
...
I created this user function
我创建了这个用户函数
T-SQL Code :
T-SQL 代码：
CREATE FUNCTION CIntToChar(@intVal Int, @intLen Int) RETURNS nvarchar(24) AS BEGIN
IF @intlen > 24
SET @intlen = 24
RETURN REPLICATE('0',@intLen-LEN(RTRIM(CONVERT(nvarchar(24),@intVal))))
+ CONVERT(nvarchar(24),@intVal) END
Example :
例子 ：
SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID
SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID
OUTPUT
输出
000867
000867
回答by Ilia Gilmijarow
Use FORMAT(<your number>,'00000000')use as many zeroes as you need to have digits in your final outcome.
使用FORMAT(<your number>,'00000000')尽可能多的零，因为您需要在最终结果中有数字。
回答by Jenna Leaf
Very straight forward way to think about padding with '0's is, if you fixed your @_int's to have 4 decimals, you inject 4 '0's:
考虑用“0”填充的非常直接的方法是，如果您将 @_int 固定为 4 个小数，则注入 4 个“0”：
select RIGHT( '0000'+ Convert(varchar, @_int), 4) as txtnum
; if your fixed space is 3, you inject 3'0's
; 如果您的固定空间为 3，则注入 3'0's
select RIGHT( '000'+ Convert(varchar, @_int), 3) as txtnum
; below I inject '00' to generate 99 labels for each bldg
; 下面我注入'00'为每个建筑生成99个标签
declare @_int int
set @_int = 1
while @_int < 100 Begin
select BldgName + '.Floor_' + RIGHT( '00'+ Convert(varchar, @_int), 2)
+ '.balcony' from dbo.tbl_FloorInfo group by BldgName
set @_int = @_int +1
End
Result is:
结果是：
'BldgA.Floor_01.balcony'
'BldgB.Floor_01.balcony'
'BldgC.Floor_01.balcony'
..
..
'BldgA.Floor_10.balcony'
'BldgB.Floor_10.balcony'
'BldgC.Floor_10.balcony'
..
..
..
'BldgA.Floor_99.balcony'
'BldgB.Floor_99.balcony'
'BldgC.Floor_99.balcony'
回答by Steve Brouillard
If I'm trying to pad to a specific total length, I use the REPLICATE and DATALENGTH functions, like so:
如果我想填充到特定的总长度，我会使用 REPLICATE 和 DATALENGTH 函数，如下所示：
DECLARE @INT INT
DECLARE @UNPADDED VARCHAR(3)
DECLARE @PADDED VARCHAR(3)
SET @INT = 2
SET @UNPADDED = CONVERT(VARCHAR(3),@INT)
SET @PADDED = REPLICATE('0', 3 - DATALENGTH(@UNPADDED)) + @UNPADDED
SELECT @INT, @UNPADDED, @PADDED
I used variables here for simplicity, but you see, you can specify the final length of the total string and not worry about the size of the INT that you start with as long as it's <= the final string length.
为简单起见，我在这里使用了变量，但是您会看到，您可以指定总字符串的最终长度，而不必担心开始的 INT 的大小，只要它 <= 最终字符串长度即可。
回答by Amy B
I always use:
我总是使用：
SET @padded = RIGHT('z0000000000000'
+ convert(varchar(30), @myInt), 8)
The z stops SQL from implicitly coverting the string into an int for the addition/concatenation.
z 阻止 SQL 将字符串隐式转换为 int 以进行加法/串联。
回答by Jim Birchall
If the int can go negative you have a problem, so to get around this I sometimes do this:
如果 int 可以变为负数，则说明有问题，因此为了解决此问题，我有时会这样做：
DECLARE @iVal int
set @iVal = -1
select
case
when @ival >= 0 then right(replicate('0',8) + cast(@ival as nvarchar(8)),8)
else '-' + right(replicate('0',8) + cast(@ival*-1 as nvarchar(8)),8)
end
回答by BobRodes
And then there's this one, using REPLICATE:
然后是这个，使用 REPLICATE：
SELECT REPLICATE('0', 7) + '1'
Of course, you can replace the literals 7 and '1' with appropriate functions as needed; the above gives you your example. For example:
当然，您可以根据需要将文字 7 和 '1' 替换为适当的函数；以上为您提供了示例。例如：
SELECT REPLICATE('0', 8 - LEN(CONVERT(nvarchar, @myInt))) + CONVERT(nvarchar, @myInt)
will pad an integer of less than 8 places with zeros up to 8 characters.
将用零填充小于 8 位的整数，最多 8 个字符。
Now, a negative number in the second argument of REPLICATE will return NULL. So, if that's a possibility (say, @myInt could be over 100 million in the above example), then you can use COALESCE to return the number without leading zeros if there are more than 8 characters:
现在，REPLICATE 的第二个参数中的负数将返回 NULL。因此，如果这是可能的（例如，在上面的示例中@myInt 可能超过 1 亿），那么如果有超过 8 个字符，您可以使用 COALESCE 返回没有前导零的数字：
SELECT COALESCE(REPLICATE('0', 8 - LEN(CONVERT(nvarchar, @myInt))) + CONVERT(nvarchar, @myInt), CONVERT(nvarchar, @myInt))
