向 varchar 字段添加前导零
我有一个字段是 varchar(8)，保存我从 float 转换为 varchar 的日期值.
I have a field that is varchar(8), holding date values that I converted from float to varchar.
有些记录有八个字符，有些记录有七个.我想通过在具有 7 的那些前导零中添加一个前导零来使它们的长度都相同.
Some records have eight characters, and some have seven. I would like to make them all the same length by adding a leading zero to the ones that have 7.
8 个字符示例:12162003
8 char example: 12162003
7 个字符示例:5072004(需要一个前导零)
7 char example: 5072004 (needs a leading zero)
查询:
select birthdate_new from table_name
推荐答案
一个适用于更多情况的函数是 REPLICATE.它将一个值 X 次连接成一个字符串.
A function that will work for more situations would be REPLICATE. It concatenates a value X amount of times to a string.
SELECT REPLICATE('0', 8-LEN(birthdate_new)) + birthdate_new AS 8_len_birthdate
这将取您的出生日期的长度，从 8 中减去它，然后将许多前导 0 放在前面，使其成为 8 个字符.
This will take the length of your birthdate, subtract it from 8, then put that many leading 0's on the front to make it 8 chars.
相关文章