在sql中增加值如001到999 [英] Increment value like 001 to 999 in sql

问题描述

如何增加和打印001格式的变量。

我只能打印1，但不能打印到1之前的零。

任何帮助都是赞。



预计：

001

002

003

004 ...

Hi, How to increment and print a variable in the format of 001.
I can print only 1, but not zeros before to 1.
Any help is appreciated.

Expected:
001
002
003
004...

推荐答案

SQL没有内置的方法来格式化带前导零的数字。但这并不复杂：

SQL has no built in way to format numbers with leading zeros. But it isn''t complicated:

SELECT RIGHT('00'+ CONVERT(VARCHAR,Id),3) FROM myTable;

SELECT replicate('0',3-LEN(Id)) + CONVERT(VARCHAR,Id) From TblNm

或

Or

DECLARE @Id INT
SET @Id = 1 --change id = 11 or 111
PRINT replicate('0',3-LEN(@Id)) + CONVERT(VARCHAR,@Id)

快乐编码！

：）

Happy Coding!
:)

请，看我的评论。当你回复我时，我会更新我的答案。



请测试一下：

Please, see my comment. When you reply me, i''ll update my answer.

Please, test it:

;WITH PseudoNumbers AS
(
	SELECT 1 AS MyVal, CAST('00001' AS NVARCHAR(5)) AS PseudoNumber
	UNION ALL
	SELECT MyVal + 1 AS MyVal, CAST(LEFT('00000', LEN('00000')-LEN(CAST(MyVal+1 AS NVARCHAR(5)))) +  CAST(MyVal+1 AS NVARCHAR(5)) AS NVARCHAR(5)) AS PseudoNumber
	FROM PseudoNumbers
	WHERE MyVal <100
)
SELECT *
FROM PseudoNumbers
--OPTION (MAXRECURSION 0)
--uncomment above line, if you want to get more than 100 records ;)
--Thank you, Code-Hunt ;) for your comment

结果：

Results:

M.. PseudoNumber
1   00001
2   00002
3   00003
4   00004
5   00005
...
100 00100

更多：

使用公用表表达式 [ ^ ]

这篇关于在sql中增加值如001到999的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！