问题描述
我有下表A:
id
----
1
2
12
123
1234
我需要用零填充 id 值:
I need to left-pad the id values with zero's:
id
----
0001
0002
0012
0123
1234
我怎样才能做到这一点?
How can I achieve this?
推荐答案
我相信这可能是您要找的:
I believe this may be what your looking for:
SELECT padded_id = REPLACE(STR(id, 4), SPACE(1), '0')
FROM tableA
或
SELECT REPLACE(STR(id, 4), SPACE(1), '0') AS [padded_id]
FROM tableA
我还没有测试第二个例子的语法.我不确定这是否 100% 有效 - 它可能需要一些调整 - 但它传达了如何获得所需输出的总体思路.
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.
编辑
要解决评论中列出的问题...
To address concerns listed in the comments...
@pkr298 - 是的，STR 只对数字有效...OP 的字段是一个 ID...因此只有数字.
@pkr298 - Yes STR does only work on numbers... The OP's field is an ID... hence number only.
@Desolator - 当然这行不通……第一个参数是 6 个字符长.您可以执行以下操作:
@Desolator - Of course that won't work... the First parameter is 6 characters long. You can do something like:
SELECT REPLACE(STR(id,
(SELECT LEN(MAX(id)) + 4 FROM tableA)), SPACE(1), '0') AS [padded_id] FROM tableA
理论上这应该移动球门柱......随着数字变大它应该总是有效......无论它是 1 还是 123456789......
this should theoretically move the goal posts... as the number gets bigger it should ALWAYS work.... regardless if its 1 or 123456789...
所以如果你的最大值是 123456...你会看到 0000123456，如果你的最小值是 1 你会看到 0000000001
So if your max value is 123456... you would see 0000123456 and if your min value is 1 you would see 0000000001
这篇关于T-SQL 中的 PadLeft 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！