string T-SQL 中的 PadLeft 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5540064/
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
PadLeft function in T-SQL
提问by Gali
I have the following table A:
我有下表A：
id
----
1
2
12
123
1234
I need to left-pad the idvalues with zero's:
我需要id用零左填充值：
id
----
0001
0002
0012
0123
1234
How can I achieve this?
我怎样才能做到这一点？
回答by Patrick
I believe this may be what your looking for:
我相信这可能是你要找的：
SELECT padded_id = REPLACE(STR(id, 4), SPACE(1), '0')
FROM tableA
or
或者
SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS [padded_id]
FROM tableA
I haven't tested the syntax on the 2nd example. I'm not sure if that works 100% - it may require some tweaking - but it conveys the general idea of how to obtain your desired output.
我还没有测试第二个例子的语法。我不确定这是否 100% 有效 - 它可能需要一些调整 - 但它传达了如何获得所需输出的总体思路。
EDIT
编辑
To address concerns listed in the comments...
为了解决评论中列出的问题...
@pkr298 - Yes STR does only work on numbers... The OP's field is an ID... hence number only.
@pkr298 - 是的 STR 只适用于数字...... OP 的字段是一个 ID......因此只有数字。
@Desolator - Of course that won't work... the First parameter is 6 characters long. You can do something like:
@Desolator - 当然这行不通……第一个参数的长度是 6 个字符。您可以执行以下操作：
SELECT REPLACE(STR(id,
(SELECT LEN(MAX(id)) + 4 FROM tableA)), SPACE(1), '0') AS [padded_id] FROM tableA
this should theoretically move the goal posts... as the number gets bigger it should ALWAYS work.... regardless if its 1 or 123456789...
这理论上应该移动球门柱......随着数字变大它应该总是工作......无论它是1还是123456789......
So if your max value is 123456... you would see 0000123456 and if your min value is 1 you would see 0000000001
所以如果你的最大值是 123456...你会看到 0000123456，如果你的最小值是 1 你会看到 0000000001
回答by CrimsonKing
SQL Server now supports the FORMATfunction starting from version 2012, so:
SQL Server 现在从 2012 版开始支持FORMAT函数，因此：
SELECT FORMAT(id, '0000') FROM TableA
will do the trick.
会做的伎俩。
If your id or column is in a varcharand represents a number you convert first:
如果您的 id 或列在 a 中varchar并代表您首先转换的数字：
SELECT FORMAT(CONVERT(INT,id), '0000') FROM TableA
回答by Mikael Eriksson
declare @T table(id int)
insert into @T values
(1),
(2),
(12),
(123),
(1234)
select right('0000'+convert(varchar(4), id), 4)
from @T
Result
结果
----
0001
0002
0012
0123
1234
回答by Marcelo Myara
Old post, but maybe this helps someone out:
旧帖子，但也许这有助于某人：
To complete until it ends up with 4 non-blank characters:
要完成直到它以 4 个非空白字符结束：
SELECT RIGHT ('0000'+COLUMNNAME, 4) FROM TABLENAME;
To complete until 10:
到 10 点完成：
SELECT RIGHT ('0000000000'+COLUMNNAME, 10) FROM TABLENAME;
In case the column is numeric, convert it to varchar first with such code:
如果列是 numeric，请先使用以下代码将其转换为 varchar ：
Select RIGHT('0000'+Convert(nvarchar(20), COLUMNNAME), 4)
From TABLENAME
And to complete until 10 with a numeric field:
并使用数字字段完成直到 10：
SELECT RIGHT ('0000000000'+Convert(nvarchar(20), COLUMNNAME), 10) FROM TABLENAME;
回答by aporia
Try this:
尝试这个：
SELECT RIGHT(REPLICATE('0',4)+CAST(Id AS VARCHAR(4)),4) FROM [Table A]
回答by Mr.Wimol Prommajorm
-- Please look into these.
——请看看这些。
select FORMAT(1, 'd4');
select FORMAT(2, 'd4');
select FORMAT(12, 'd4');
select FORMAT(123, 'd4');
select FORMAT(1234, 'd4');
-- I hope these would help you
——希望这些对你有帮助
回答by Piotr Nawrot
This works for strings, integers and numeric:
这适用于字符串、整数和数字：
SELECT CONCAT(REPLICATE('0', 4 - LEN(id)), id)
SELECT CONCAT(REPLICATE('0', 4 - LEN(id)), id)
Where 4is desired length. Works for numbers with more than 4 digits, returns empty string on NULLvalue.
哪里4是所需的长度。适用于超过 4 位的数字，在NULL值上返回空字符串。
回答by kfed
If someone is still interested, I found this article on DATABASE.GUIDE:
Left Padding in SQL Server – 3 LPAD() Equivalents
如果有人仍然感兴趣，我在 DATABASE.GUIDE:Left Padding in SQL Server – 3 LPAD() Equivalents上找到了这篇文章
In short, there are 3 methods mentioned in that article.
Let's say your id=12 and you need it to display as 0012.
简而言之，该文章中提到了3种方法。
假设您的 id=12 并且您需要将其显示为 0012。
Method 1 – Use the RIGHT() Function
The first method uses the RIGHT() function to return only the rightmost part of the string, after adding some leading zeros.
方法 1 – 使用 RIGHT() 函数
第一种方法使用 RIGHT() 函数在添加一些前导零后仅返回字符串的最右边部分。
SELECT RIGHT('00' + '12', 4);
SELECT RIGHT('00' + '12', 4);
Result:0012
结果：0012
Method 2 – Use a Combination of RIGHT() and REPLICATE()
This method is almost the same as the previous method, with the only difference being that I simply replace the three zeros with the REPLICATE() function:
方法二——结合使用 RIGHT() 和 REPLICATE()
这个方法和前面的方法几乎一样，唯一的区别是我简单地用 REPLICATE() 函数替换了三个零：
SELECT RIGHT(REPLICATE('0', 2) + '12', 4);
SELECT RIGHT(REPLICATE('0', 2) + '12', 4);
Result:0012
结果：0012
Method 3 – Use a Combination of REPLACE() and STR()
This method comes from a completely different angle to the previous methods:
方法三——结合使用 REPLACE() 和 STR()
这种方法与之前的方法从一个完全不同的角度出发：
SELECT REPLACE(STR('12', 4),' ','0');
SELECT REPLACE(STR('12', 4),' ','0');
Result:0012
结果：0012
Check out the article, there is more in depth analysis with examples.
查看文章，有更深入的分析和示例。
回答by KenB
This is what I normally use when I need to pad a value.
当我需要填充一个值时，这是我通常使用的。
SET @PaddedValue = REPLICATE('0', @Length - LEN(@OrigValue)) + CAST(@OrigValue as VARCHAR)
回答by mehrdad
Create Function :
创建函数：
Create FUNCTION [dbo].[PadLeft]
(
@Text NVARCHAR(MAX) ,
@Replace NVARCHAR(MAX) ,
@Len INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @var NVARCHAR(MAX)
SELECT @var = ISNULL(LTRIM(RTRIM(@Text)) , '')
RETURN RIGHT(REPLICATE(@Replace,@Len)+ @var, @Len)
END
Example:
例子：
Select dbo.PadLeft('123456','0',8)
