SQL 数字小于10时如何添加前导零？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36930767/
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 add leading zero when number is less than 10?
提问by RedRocket
I have a column in my sql table. I am wondering how can I add leading zero to my column when my column's value is less than 10? So for example:
我的 sql 表中有一个列。我想知道当我的列的值小于 10 时，如何将前导零添加到我的列中？例如：
number result
1 -> 01
2 -> 02
3 -> 03
4 -> 04
10 -> 10
回答by CrimsonKing
format(number,'00')
Version >= 2012
版本 >= 2012
回答by Felix Pamittan
You can use RIGHT:
您可以使用RIGHT：
SELECT RIGHT('0' + CAST(Number AS VARCHAR(2)), 2) FROM tbl
For Numbers with length > 2, you use a CASEexpression:
对于Number长度 > 2 的 s，使用CASE表达式：
SELECT
CASE
WHEN Number BETWEEN 0 AND 99
THEN RIGHT('0' + CAST(Number AS VARCHAR(2)), 2)
ELSE
CAST(Number AS VARCHAR(10))
END
FROM tbl
回答by APH
Felix's solution is great, unless you have numbers above 100 in your table, in which case, try using a case statement instead:
Felix 的解决方案很棒，除非您的表中有超过 100 的数字，在这种情况下，请尝试使用 case 语句：
Select case when Number between 1 and 9
then '0' else '' end + cast(number as varchar(3))
回答by KumarHarsh
another method,
另一种方法，
select case when number <10 then replicate('0',1)+cast(number as varchar)
else cast(number as varchar) end
回答by Shnugo
And yet one more solution:
还有一个解决方案：
SELECT REPLACE(STR(@YourNumber,2),' ','0');
I prefer this, as other approaches might lead to random results in case of numbers which are wider than the count of digits you've specified:
我更喜欢这个，因为如果数字比您指定的数字数更宽，其他方法可能会导致随机结果：
But it will not deal with negativ numbers...
但它不会处理负数......
DECLARE @YourNumber INT=123;
SELECT REPLACE(STR(@YourNumber,5),' ', '0') --pad to five digits
,REPLACE(STR(@YourNumber,3),' ', '0') --pad to 3 digits
,REPLACE(STR(@YourNumber,2),' ', '0'); --123 is wider than 2 digits...
The result
结果
00123
123
**
