SQL 如何在sql server中将数字转换为前缀为0的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15674236/
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 to cast a number to string with 0 prefix in sql server
提问by Buzz
In sql server, if I have a number n, and n>=0 and n<=100, how to cast it into a string with sql?
在sql server中，如果我有一个数字n，和n>=0 and n<=100，如何用sql将它转换成一个字符串？
1 => '01'
2 => '02'
...
10 => '10'
99 => '99'
It is something like printf.
它类似于 printf 。
printf ('%2d", n);
回答by Steve Kass
So long as n is strictly lessthan 100, you can use
只要 n 严格小于100，就可以使用
RIGHT(100+n,2)
In SQL Server 2012, you can also use FORMAT, and the following will work and also give you '100' for 100.
在 SQL Server 2012 中，您还可以使用 FORMAT，以下内容将起作用，并为您提供 100 的“100”。
FORMAT(n,'00','en-US')
回答by praveen
%2dneeds to translated to 2 digitformat in sql server
%2d需要2 digit在 sql server 中转换为格式
Try this
尝试这个
Declare @val int
set @val=1
SELECT RIGHT('0' + CAST(@val AS VARCHAR(2)), 2)
Result :-
结果 ：-
01
In order to check for numbers between 0 to 100use casestatement
为了检查0 to 100usecase语句之间的数字
SELECT case when @val>0 and @val<100 then
RIGHT('0' + CAST(@val AS VARCHAR), 2)
else
NULL
END
回答by Moho
select RIGHT('00'+ convert(varchar, @val), 3)
回答by rsbarro
Yet another approach (not sure how you want to handle numbers greater than 99):
另一种方法（不确定你想如何处理大于 99 的数字）：
DECLARE @numbers AS TABLE(Val INT)
INSERT INTO @numbers VALUES(1)
INSERT INTO @numbers VALUES(2)
INSERT INTO @numbers VALUES(10)
INSERT INTO @numbers VALUES(11)
INSERT INTO @numbers VALUES(98)
INSERT INTO @numbers VALUES(99)
INSERT INTO @numbers VALUES(100)
SELECT REPLACE(STR(Val, 2), ' ', '0') FROM @numbers
回答by Ken Clark
Do this:
做这个：
DECLARE @table AS TABLE(n INT)
INSERT INTO @table
VALUES(1),(3),(5),(9),(10),(50),(99),(100)
Select Case When n<10 Then '0'+Cast(n as varchar(10)) Else Cast(n as varchar(10)) End From @table
回答by Nisar
SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS YY FROM tableA
This would smart way to solve your problem...
这将是解决您问题的明智方法...
