blocks|key|2023478|text|使用REPLICATE，这样你就不必硬编码所有的前导零：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2023479|DECLARE+@InputStr+int
+++++++,@Size+++++int
SELECT+@InputStr=123
++++++,@Size=10

PRINT+REPLICATE('0',@Size-LEN(RTRIM(CONVERT(varchar(8000),@InputStr))))+%2B+CONVERT(varchar(8000),@InputStr)|code-block|syntax|javascript|2023480|输出：|2023481|0000000123|2023482|entityMap^0|2|9|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|P|$]]

Use <code>REPLICATE</code> so you don't have to hard code all the leading zeros:

<pre><code>DECLARE @InputStr int
 ,@Size int
SELECT @InputStr=123
 ,@Size=10

PRINT REPLICATE('0',@Size-LEN(RTRIM(CONVERT(varchar(8000),@InputStr)))) + CONVERT(varchar(8000),@InputStr)
</code></pre>

OUTPUT:

<pre><code>0000000123
</code></pre>

blocks|key|2023585|text|数据类型"+int“不能超过10位数字，另外"+len()”函数适用于int，因此在调用len()函数之前不需要将int转换为字符串。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023586|最后，您没有考虑到int的大小>您希望将其填充到的位数(@intLen)的情况。|2023587|因此，更简洁/正确的解决方案是：|2023588|CREATE+FUNCTION+rf_f_CIntToChar(@intVal+Int,+@intLen+Int)+RETURNS+nvarchar(10)
AS
BEGIN
++++IF+@intlen+>+20+SET+@intlen+=+20
++++IF+@intlen+<+LEN(@intVal)+RETURN+RIGHT(CONVERT(nvarchar(10),+@intVal),+@intlen)
++++RETURN+REPLICATE('0',+@intLen+-+LEN(@intVal))+%2B+CONVERT(nvarchar(10),+@intVal)
END|code-block|syntax|javascript|2023589|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Data type "int" cannot be more than 10 digits, additionally the "Len()" function works on ints, so there's no need to convert the int to a string before calling the len() function.

Lastly, you are not taking into account the case where the size of your int > the total number of digits you want it padded to (@intLen). 

Therefore, a more concise / correct solution is:

<pre><code>CREATE FUNCTION rf_f_CIntToChar(@intVal Int, @intLen Int) RETURNS nvarchar(10)
AS
BEGIN
 IF @intlen &gt; 20 SET @intlen = 20
 IF @intlen &lt; LEN(@intVal) RETURN RIGHT(CONVERT(nvarchar(10), @intVal), @intlen)
 RETURN REPLICATE('0', @intLen - LEN(@intVal)) + CONVERT(nvarchar(10), @intVal)
END
</code></pre>

blocks|key|1810264|text|这个问题不是高调的，但需要注意的是，您需要使用(N)VARCHAR而不是(N)CHAR数据类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1810265|RIGHT('000'+%2B+CAST(@number1+AS+NCHAR(3)),+3+)|code-block|syntax|javascript|1810266|不会从SQL+2005生成正确的响应。|1810267|RIGHT('000'+%2B+CAST(@number1+AS+NVARCHAR(3)),+3+)|1810268|将从SQL+2005产生所需的响应。|1810269|varchar将为您提供所需的前缀长度。其中，固定长度数据类型将需要指定和调整长度。|1810270|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Not to High-Jack this question but the note needs to be made that you need to use (N)VARCHAR instead of (N)CHAR data-type.

<pre><code>RIGHT('000' + CAST(@number1 AS NCHAR(3)), 3 )
</code></pre>

Above segment, will not produce the correct response from SQL 2005

<pre><code>RIGHT('000' + CAST(@number1 AS NVARCHAR(3)), 3 )
</code></pre>

Above segment, will produce the desired response from SQL 2005

The the varchar will provide you with the desired prefix length on the fly.
Where as the fixed length data type will require length designation and adjustments.

blocks|key|1810383|text|试试这个：select+right('00000'+%2B+cast(Your_Field+as+varchar(5)),+5)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1810384|它将得到5位数的结果，例如:+00001，...，01234|1810385|entityMap^0|5|1L|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

Try this: <code>select right('00000' + cast(Your_Field as varchar(5)), 5)</code>

It will get the result in 5 digits, ex: 00001,...., 01234

blocks|key|1810441|text|++++select+right('000'+%2B+convert(varchar(3),id),3)+from+table

++++example
++++declare+@i+int
++++select+@i+=1

select+right('000'+%2B+convert(varchar(3),@i),3)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1810442|顺便说一句，如果它是一个整型列，那么它仍然不会保留零，只是在表示层中保留，或者如果您确实需要在SELECT中保留|unstyled|1810443|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> select right('000' + convert(varchar(3),id),3) from table

 example
 declare @i int
 select @i =1

select right('000' + convert(varchar(3),@i),3)
</code></pre>

BTW if it is an int column then it will still not keep the zeros
Just do it in the presentation layer or if you really need to in the
SELECT

blocks|key|2023830|text|也有同样的问题，这就是我如何解决的.简单而优雅。"4“是字符串应该有多长，不管传递的是什么长度的整数，它都会填充0直到"4”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023831|STUFF(SomeVariableOrField,1,0,REPLICATE('0',4+-+LEN(SomeVariableOrField)))|code-block|syntax|javascript|2023832|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|$]]

Had same issue, this is how I resolved... Simple and elegant. The "4" is how long the string should be, no matter what length of integer is passed it will pad with zero's up to "4".

<pre><code>STUFF(SomeVariableOrField,1,0,REPLICATE('0',4 - LEN(SomeVariableOrField)))
</code></pre>

blocks|key|2023834|text|我喜欢用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023835|DECLARE+@Length+int
DECLARE+@Number+int
SET+@Length+=+9
SET+@Number+=+4

select+right(+POWER(10,+@Length)+%2B+@Number,+@Length)|code-block|syntax|javascript|2023836|这给了我|2023837|000000004|2023838|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I like to use 

<pre><code>DECLARE @Length int
DECLARE @Number int
SET @Length = 9
SET @Number = 4

select right( POWER(10, @Length) + @Number, @Length)
</code></pre>

this gives me 

<pre><code>000000004
</code></pre>

blocks|key|2023851|text|DECLARE+@number1+INT,+@number2+INT

SET+@number1+=+1

SET+@number2+=+867|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2023852|--没有'RTRIM'，返回值为3__！|unstyled|offset|length|style|CODE|2023853|SELECT+RIGHT('000'+%2B+RTRIM(CAST(@number1+AS+NCHAR(3)),+3+))+AS+NUMBER_CONVERTED|2023854|--没有'RTRIM'，返回值为867___！|2023855|SELECT+RIGHT('000000'+%2B+RTRIM(CAST(@number2+AS+NCHAR(6)),+6+))+AS+NUMBER_CONVERTED|2023856|entityMap^0|0|G|3|0|0|G|6|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|T|8|@$G|U|H|V|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$B|C]]|$1|M|3|N|5|F|7|X|8|@$G|Y|H|Z|I|J]]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$B|C]]|$1|Q|3|-4|5|F|7|11|8|@]|9|@]|A|$]]]|R|$]]

<pre><code>DECLARE @number1 INT, @number2 INT

SET @number1 = 1

SET @number2 = 867
</code></pre>

-- Without the 'RTRIM', the value returned is <code>3__</code> !!!

<pre><code>SELECT RIGHT('000' + RTRIM(CAST(@number1 AS NCHAR(3)), 3 )) AS NUMBER_CONVERTED
</code></pre>

-- Without the 'RTRIM', the value returned is <code>867___</code> !!! 

<pre><code>SELECT RIGHT('000000' + RTRIM(CAST(@number2 AS NCHAR(6)), 6 )) AS NUMBER_CONVERTED
</code></pre>

blocks|key|2023898|text|我在想，这对你有什么用吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2023899|declare+@val+int,@len=800
select+replace(str(@val,@len),'+','0')|code-block|syntax|javascript|2023900|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|$]]

I was wondering, would this be on any use to you?

<pre><code>declare @val int,@len=800
select replace(str(@val,@len),' ','0')
</code></pre>

blocks|key|1810652|text|这对我在MYSQL中是有效的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1810653|FUNCTION+leadingZero(format+VARCHAR(255),+num+VARCHAR(255))
++RETURNS+varchar(255)+CHARSET+utf8
BEGIN
++return+CONCAT(SUBSTRING(format,1,LENGTH(format)-LENGTH(num)),num);
END|code-block|syntax|javascript|1810654|例如：|1810655|leadingZero('000',999);+returns+'999'
leadingZero('0000',999);+returns+'0999'
leadingZero('xxxx',999);+returns+'x999'|1810656|希望这能有所帮助。诚挚的问候|1810657|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|$]]

This work for me in MYSQL:

<pre><code>FUNCTION leadingZero(format VARCHAR(255), num VARCHAR(255))
 RETURNS varchar(255) CHARSET utf8
BEGIN
 return CONCAT(SUBSTRING(format,1,LENGTH(format)-LENGTH(num)),num);
END
</code></pre>

For example:

<pre><code>leadingZero('000',999); returns '999'
leadingZero('0000',999); returns '0999'
leadingZero('xxxx',999); returns 'x999'
</code></pre>

Hope this will help.
Best regards

blocks|key|2024192|text|使用RIGHT是一个很好的选择，但您也可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2024193|SUBSTRING(CAST((POWER(10,+N)+%2B+value)+AS+NVARCHAR(N+%2B+1)),+2,+N)|code-block|syntax|javascript|2024194|其中N是要显示的总位数。因此，对于带有前导零的3位数值(N+=+3)：|2024195|SUBSTRING(CAST((POWER(10,+3)+%2B+value)+AS+NVARCHAR(4)),+2,+3)|2024196|或者另一种方式|2024197|SUBSTRING(CAST(1000+%2B+value)+AS+NVARCHAR(4)),+2,+3)|2024198|这样做的目的是将所需的值与10的幂相加，该幂大到足以生成足够多的前导0，然后去掉前导1。|2024199|这种技术的优点是结果总是相同的长度，允许使用SUBSTRING而不是RIGHT，后者在一些查询语言(如HQL)中不可用。|2024200|entityMap^0|2|5|0|0|2|1|S|5|0|0|0|0|0|M|9|Y|5|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|17|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|18|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|19|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1A|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|1B|8|@$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|X|$]]

Using <code>RIGHT</code> is a good option but you can also do the following:

<pre><code>SUBSTRING(CAST((POWER(10, N) + value) AS NVARCHAR(N + 1)), 2, N)
</code></pre>

where <code>N</code> is the total number of digits you want displayed. So for a 3-digit value with leading zeros (<code>N = 3</code>):

<pre><code>SUBSTRING(CAST((POWER(10, 3) + value) AS NVARCHAR(4)), 2, 3)
</code></pre>

or alternately

<pre><code>SUBSTRING(CAST(1000 + value) AS NVARCHAR(4)), 2, 3)
</code></pre>

What this is doing is adding the desired value to a power of 10 large enough to generate enough leading zeros, then chopping off the leading 1.

The advantage of this technique is that the result will always be the same length, allowing the use of <code>SUBSTRING</code> instead of <code>RIGHT</code>, which is not available in some query languages (like HQL).

blocks|key|1810958|text|适用于SQL+Server+2008或更高版本的单行解决方案(本身)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1810959|DECLARE+@DesiredLenght+INT+=+20;
SELECT+
++++CONCAT(
++++++++REPLICATE(
++++++++++++'0',
++++++++++++(@DesiredLenght-LEN([Column]))+*+(1%2BSIGN(@DesiredLenght-LEN([Column]))+/+2)+),
++++++++[Column])
FROM+Table;|code-block|syntax|javascript|1810960|SIGN表达式的乘法等同于MAX(0,+@DesiredLenght-LEN([Column]))。问题是MAX()只接受一个参数...|offset|length|style|CODE|1810961|entityMap^0|0|0|0|4|D|10|1H|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]|$I|T|J|U|K|L]|$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|N|$]]

One line solution (per se) for SQL Server 2008 or above:

<pre><code>DECLARE @DesiredLenght INT = 20;
SELECT 
 CONCAT(
 REPLICATE(
 '0',
 (@DesiredLenght-LEN([Column])) * (1+SIGN(@DesiredLenght-LEN([Column])) / 2) ),
 [Column])
FROM Table;
</code></pre>

Multiplication by <code>SIGN</code> expression is equivalent to <code>MAX(0, @DesiredLenght-LEN([Column]))</code>. The problem is that <code>MAX()</code> accepts only one argument...

blocks|key|1810986|text|在SQLServer中工作|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1810987|declare+@myNumber+int+=+123
declare+@leadingChar+varchar(1)+=+'0'
declare+@numberOfLeadingChars+int+=+5

select+right(REPLICATE+(+@leadingChar+,+@numberOfLeadingChars+)+%2B+cast(@myNumber+as+varchar(max)),+@numberOfLeadingChars)|code-block|syntax|javascript|1810988|尽情享受|1810989|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Works in SQLServer 

<pre><code>declare @myNumber int = 123
declare @leadingChar varchar(1) = '0'
declare @numberOfLeadingChars int = 5

select right(REPLICATE ( @leadingChar , @numberOfLeadingChars ) + cast(@myNumber as varchar(max)), @numberOfLeadingChars)
</code></pre>

Enjoy

I need to convert int datafield to nvarchar with leading zeros

example:

1 convert to '001'

867 convert to '000867', etc.

thx.

<hr>

This is my response 4 Hours later ...

I tested this T-SQL Script and work fine for me !

<pre><code>DECLARE @number1 INT, @number2 INT

SET @number1 = 1

SET @number2 = 867

SELECT RIGHT('000' + CAST(@number1 AS NCHAR(3)), 3 ) AS NUMBER_CONVERTED

SELECT RIGHT('000000' + CAST(@number2 AS NCHAR(6)), 6 ) AS NUMBER_CONVERTED
</code></pre>

<hr>

I created this user function

T-SQL Code :

<pre><code>CREATE FUNCTION CIntToChar(@intVal BIGINT, @intLen Int) RETURNS nvarchar(20)
AS
BEGIN

 -- BIGINT = 2^63-1 (9,223,372,036,854,775,807) Max size number

 -- @intlen contains the string size to return
 IF @intlen &gt; 20
 SET @intlen = 20

 RETURN REPLICATE('0',@intLen-LEN(RTRIM(CONVERT(nvarchar(20),@intVal)))) 
 + CONVERT(nvarchar(20),@intVal)

END
</code></pre>

Example :

SELECT dbo.CIntToChar( 867, 6 ) AS COD_ID

OUTPUT

000867

How to convert int to char with leading zeros?

SQL Server

我需要将int数据字段转换为带有前导零的nvarchar示例：%1转换为“001”867转换为'000867'，等等。谢谢。这是我4小时后的回复...我测试了这个T-SQL脚本，对我来说工作得很好！DECLARE @number1 INT, @number2 INTSET @number1 = 1SET @number...

问如何将int转换为带有前导零的char？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将int转换为带有前导零的char？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将int转换为带有前导零的char？
EN