将前导 0 添加到 sql 中的 int 的最有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/778909/
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
Most efficient method for adding leading 0's to an int in sql
提问by Mike Bennett
I need to return two fields from a database concatenated as 'field1-field2'. The second field is an int, but needs to be returned as a fixed length of 5 with leading 0's. The method i'm using is:
我需要从连接为“field1-field2”的数据库中返回两个字段。第二个字段是一个整数，但需要以固定长度 5 和前导 0 的形式返回。我使用的方法是：
SELECT Field1 + '-' + RIGHT('0000' + CAST(Field2 AS varchar),5) FROM ...
Is there a more efficient way to do this?
有没有更有效的方法来做到这一点？
回答by SQLMenace
That is pretty much the way: Adding Leading Zeros To Integer Values
这几乎就是这样：向整数值添加前导零
So, to save following the link, the query looks like this, where #Numbersis the table and Numis the column:
所以，为了保存链接，查询看起来像这样，#Numbers表在哪里Num，列在哪里：
SELECT RIGHT('000000000' + CONVERT(VARCHAR(8),Num), 8) FROM #Numbers
for negative or positive values
对于负值或正值
declare @v varchar(6)
select @v = -5
SELECT case when @v < 0
then '-' else '' end + RIGHT('00000' + replace(@v,'-',''), 5)
回答by DJ.
Another way (without CAST or CONVERT):
另一种方式（没有 CAST 或 CONVERT）：
SELECT RIGHT(REPLACE(STR(@NUM),' ','0'),5)
回答by Petros
If you can afford/want to have a function in your database you could use something like:
如果您负担得起/希望在您的数据库中有一个函数，您可以使用以下内容：
CREATE FUNCTION LEFTPAD
(@SourceString VARCHAR(MAX),
@FinalLength INT,
@PadChar CHAR(1))
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN
(SELECT Replicate(@PadChar, @FinalLength - Len(@SourceString)) + @SourceString)
END
回答by Mitchel Sellers
I would do it like this.
我会这样做。
SELECT RIGHT(REPLICATE('0', 5) + CAST(Field2 AS VARCHAR(5),5)
Not necessarily all that "Easier", or more efficient, but better to read. Could be optimized to remove the need for "RIGHT"
不一定都是“更容易”或更高效，但更好读。可以优化以消除对“正确”的需要
回答by user2662753
If you want to get a consistent number of total strings in the final result by adding different number of zeros, here is a little bit modification (for vsql)
如果你想通过添加不同数量的零在最终结果中获得一致数量的总字符串，这里有一点修改（对于vsql）
SELECT
CONCAT(
REPEAT('0', 9-length(TO_CHAR(var1))),
CAST(var1 AS VARCHAR(9))
) as var1
You can replace 9 by any number for your need!
您可以根据需要用任意数字替换 9！
BRD
BRD
