使用格式将 SQL 中的数字转换为 varchar
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1764374/
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
Convert number to varchar in SQL with formatting
提问by Jeff Stock
Is there a way in T-SQL to convert a TINYINT to VARCHAR with custom number formatting? For instance, my TINYINT has a value of 3 and I want to convert it to a VARCH of 03, so that it always shows a 2 digit number.
T-SQL 中有没有办法使用自定义数字格式将 TINYINT 转换为 VARCHAR？例如，我的 TINYINT 的值为 3，我想将其转换为 03 的 VARCH，以便它始终显示 2 位数字。
I don't see this ability in the CONVERT function.
我在 CONVERT 函数中没有看到这种能力。
回答by Tomalak
RIGHT('00' + CONVERT(VARCHAR, MyNumber), 2)
Be warned that this will cripple numbers > 99. You might want to factor in that possibility.
请注意，这会削弱大于 99 的数字。您可能需要考虑这种可能性。
回答by kevchadders
Use the RIGHT function... e.g.
使用正确的功能......例如
DECLARE @testnum TINYINT
SET @testnum = 3
PRINT RIGHT('00' + CONVERT(VARCHAR(2), @testnum), 2)
回答by Adriaan Stander
You can try this
你可以试试这个
DECLARE @Table TABLE(
Val INT
)
INSERT INTO @Table SELECT 3
INSERT INTO @Table SELECT 30
DECLARE @NumberPrefix INT
SET @NumberPrefix = 2
SELECT REPLICATE('0', @NumberPrefix - LEN(Val)) + CAST(Val AS VARCHAR(10))
FROM @Table
回答by Newfave
What is the value range? Is it 0 through 10? If so, then try:
取值范围是多少？是 0 到 10 吗？如果是这样，请尝试：
SELECT REPLICATE('0',2-LEN(@t)) + CAST(@t AS VARCHAR)
That handles 0 through 9 as well as 10 through 99.
它处理 0 到 9 以及 10 到 99。
Now, tinyint can go up to the value of 255. If you want to handle > 99 through 255, then try this solution:
现在，tinyint 可以达到 255 的值。如果要处理 > 99 到 255，请尝试以下解决方案：
declare @t TINYINT
set @t =233
SELECT ISNULL(REPLICATE('0',2-LEN(@t)),'') + CAST(@t AS VARCHAR)
To understand the solution, the expression to the left of the + calculates the number of zeros to prefix to the string.
为了理解该解决方案，+ 左侧的表达式计算字符串前缀的零数。
In case of the value 3, the length is 1. 2 - 1 is 1. REPLICATE Adds one zero. In case of the value 10, the length is 2. 2 - 2 is 0. REPLICATE Adds nothing. In the case of the value 100, the length is -1 which produces a NULL. However, the null value is handled and set to an empty string.
在值为 3 的情况下，长度为 1。2 - 1 为 1。 REPLICATE 添加一个零。如果值为 10，则长度为 2。2 - 2 为 0。 REPLICATE 不添加任何内容。在值为 100 的情况下，长度为 -1，这会产生 NULL。但是，将处理空值并将其设置为空字符串。
Now if you decide that because tinyint can contain up to 255 and you want your formatting as three characters, just change the 2-LEN to 3-LEN in the left expression and you're set.
现在，如果您决定因为 tinyint 最多可包含 255 个字符，并且您希望将格式设置为三个字符，只需将左侧表达式中的 2-LEN 更改为 3-LEN 即可。
回答by Rippo
declare @t tinyint
set @t =3
select right(replicate('0', 2) + cast(@t as varchar),2)
Ditto: on the cripping effect for numbers > 99
同上：关于数字 > 99 的剪裁效果
If you want to cater for 1-255 then you could use
如果您想满足 1-255 的需求，那么您可以使用
select right(replicate('0', 2) + cast(@t as varchar),3)
But this would give you 001, 010, 100 etc
但这会给你 001、010、100 等
回答by Niren Pillay
Here's an alternative following the last answer
这是最后一个答案之后的替代方案
declare @t tinyint,@v tinyint
set @t=23
set @v=232
Select replace(str(@t,4),' ','0'),replace(str(@t,5),' ','0')
This will work on any number and by varying the length of the str()function you can stipulate how many leading zeros you require. Provided of course that your string length is always >= maximum number of digits your number type can hold.
这适用于任何数字，通过改变str()函数的长度，您可以规定您需要多少个前导零。当然，前提是您的字符串长度始终 >= 您的号码类型可以容纳的最大位数。
回答by happycamper
Had the same problem with a zipcode field. Some folks sent me an excel file with zips, but they were formatted as #'s. Had to convert them to strings as well as prepend leading 0's to them if they were < 5 len ...
邮政编码字段也有同样的问题。有些人给我发送了一个带有 zip 的 excel 文件，但它们被格式化为 #'s。如果它们 < 5 len ，则必须将它们转换为字符串并在它们前面加上前导 0 ...
declare @int tinyint
set @int = 25
declare @len tinyint
set @len = 3
select right(replicate('0', @len) + cast(@int as varchar(255)), @len)
You just alter the @len to get what you want. As formatted, you'll get...
您只需更改@len 即可获得所需内容。格式化后，你会得到...
001
002
...
010
011
...
255
Ideally you'd "varchar(@len)", too, but that blows up the SQL compile. Have to toss an actual # into it instead of a var.
理想情况下，您也会使用“varchar(@len)”，但这会破坏 SQL 编译。必须将实际的 # 放入其中而不是 var。
回答by Antonio
Correción: 3-LEN
更正：3-LEN
declare @t TINYINT
set @t =233
SELECT ISNULL(REPLICATE('0',3-LEN(@t)),'') + CAST(@t AS VARCHAR)
