blocks|key|1187064|text|RIGHT('00'+%2B+CONVERT(VARCHAR,+MyNumber),+2)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1187065|需要注意的是，这将使数字大于99。您可能希望将这种可能性考虑在内。|unstyled|1187066|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>RIGHT('00' + CONVERT(VARCHAR, MyNumber), 2)
</code></pre>

Be warned that this will cripple numbers > 99. You might want to factor in that possibility.

blocks|key|1402465|text|使用正确的函数...例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402466|DECLARE+@testnum+TINYINT
SET+@testnum+=+3
PRINT+RIGHT('00'+%2B+CONVERT(VARCHAR(2),+@testnum),+2)|code-block|syntax|javascript|1402467|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use the RIGHT function...
e.g.

<pre><code>DECLARE @testnum TINYINT
SET @testnum = 3
PRINT RIGHT('00' + CONVERT(VARCHAR(2), @testnum), 2)
</code></pre>

blocks|key|1402472|text|你可以试试这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402473|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))+%2B+CAST(Val+AS+VARCHAR(10))
FROM++++@Table|code-block|syntax|javascript|1402474|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can try this

<pre><code>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
</code></pre>

blocks|key|1187214|text|取值范围是什么？是从0到10吗？如果是这样，那么尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1187215|SELECT+REPLICATE('0',2-LEN(@t))+%2B+CAST(@t+AS+VARCHAR)|code-block|syntax|javascript|1187216|它处理0到9以及10到99。|1187217|现在，tinyint可以达到255的值。如果您希望处理>+99到255，请尝试此解决方案：|1187218|declare+@t++TINYINT
set+@t+=233
SELECT+ISNULL(REPLICATE('0',2-LEN(@t)),'')+%2B+CAST(@t+AS+VARCHAR)|1187219|为了理解解决方案，%2B左边的表达式计算字符串的前缀为零的数量。|1187220|在值为3的情况下，长度为1.2。2-1为1。REPLICATE添加一个零。在值为10的情况下，长度为2.2。2-2为0。REPLICATE不添加任何内容。在值为100的情况下，长度为-1，这将产生NULL。但是，将处理空值并将其设置为空字符串。|1187221|现在，如果您决定因为tinyint最多可以包含255个字符，并且您希望将格式设置为三个字符，那么只需将左侧表达式中的2-LEN更改为3-LEN，您就完成了设置。|1187222|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

What is the value range? Is it 0 through 10? If so, then try:

<pre><code>SELECT REPLICATE('0',2-LEN(@t)) + CAST(@t AS VARCHAR)
</code></pre>

That handles 0 through 9 as well as 10 through 99.

Now, tinyint can go up to the value of 255. If you want to handle > 99 through 255, then try this solution:

<pre><code>declare @t TINYINT
set @t =233
SELECT ISNULL(REPLICATE('0',2-LEN(@t)),'') + CAST(@t AS VARCHAR)
</code></pre>

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.

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.

blocks|key|1402439|text|declare+@t++tinyint
set+@t+=3
select+right(replicate('0',+2)+%2B+cast(@t+as+varchar),2)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1402440|同上:关于数字>+99的抄袭效果|unstyled|1402441|如果你想满足1-255的需求，你可以使用|1402442|select+right(replicate('0',+2)+%2B+cast(@t+as+varchar),3)|1402443|但是这会给你001,010,100等等|1402444|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>declare @t tinyint
set @t =3
select right(replicate('0', 2) + cast(@t as varchar),2)
</code></pre>

Ditto: on the cripping effect for numbers > 99

If you want to cater for 1-255 then you could use

<pre><code>select right(replicate('0', 2) + cast(@t as varchar),3)
</code></pre>

But this would give you 001, 010, 100 etc

blocks|key|1402724|text|下面是最后一个答案之后的另一个选择|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402725|declare+@t+tinyint,@v+tinyint
set+@t=23
set+@v=232
Select+replace(str(@t,4),'+','0'),replace(str(@t,5),'+','0')|code-block|syntax|javascript|1402726|这将适用于任何数字，并且通过改变str()函数的长度，您可以指定需要多少个前导零。当然，假设您的字符串长度始终为>=，您的number类型可以容纳的最大位数。|offset|length|style|CODE|1402727|entityMap^0|0|0|G|5|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Here's an alternative following the last answer

<pre><code>declare @t tinyint,@v tinyint
set @t=23
set @v=232
Select replace(str(@t,4),' ','0'),replace(str(@t,5),' ','0')
</code></pre>

This will work on any number and by varying the length of the <code>str()</code> 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.

blocks|key|1402633|text|更正:3-镜头|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402634|declare+@t++TINYINT+
set+@t+=233
SELECT+ISNULL(REPLICATE('0',3-LEN(@t)),'')+%2B+CAST(@t+AS+VARCHAR)+|code-block|syntax|javascript|1402635|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Correción: 3-LEN

<pre><code>declare @t TINYINT 
set @t =233
SELECT ISNULL(REPLICATE('0',3-LEN(@t)),'') + CAST(@t AS VARCHAR) 
</code></pre>

blocks|key|1402801|text|邮政编码字段也有同样的问题。有些人给我发了一个带有zip的excel文件，但它们被格式化为#'s。如果它们小于5个长度，就必须将它们转换为字符串，并在它们前面加上前导0。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1402802|declare+@int+tinyint
set+++++@int+=+25
declare+@len+tinyint
set+++++@len+=+3

select+right(replicate('0',+@len)+%2B+cast(@int+as+varchar(255)),+@len)|code-block|syntax|javascript|1402803|您只需更改@len即可获得您想要的内容。格式化后，您将得到...|1402804|001
002
...
010
011
...
255|1402805|理想情况下，您也应该使用"varchar(@len)"，但这会破坏SQL编译器。必须抛出一个实际的#，而不是var。|1402806|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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 &lt; 5 len ...

<pre><code>declare @int tinyint
set @int = 25
declare @len tinyint
set @len = 3

select right(replicate('0', @len) + cast(@int as varchar(255)), @len)
</code></pre>

You just alter the @len to get what you want. As formatted, you'll get...

<pre><code>001
002
...
010
011
...
255
</code></pre>

Ideally you'd "varchar(@len)", too, but that blows up the SQL compile. Have to toss an actual # into it instead of a var.

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.

I don't see this ability in the CONVERT function.

Convert number to varchar in SQL with formatting

编译器

在T-SQL中有没有一种方法可以将TINYINT转换为具有自定义数字格式的VARCHAR？例如，我的TINYINT的值为3，我希望将其转换为VARCH值03，以便它始终显示一个2位数字。我在CONVERT函数中看不到这种能力。

问在SQL中使用格式化将数字转换为varchar
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL中使用格式化将数字转换为varcharEN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL中使用格式化将数字转换为varchar
EN