向 varchar 字段添加前导零 [英] Add leading zeros to a varchar field

问题描述

我有一个字段是 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.

这篇关于向 varchar 字段添加前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！