SQL 向 varchar 字段添加前导零

Add leading zeros to a varchar field

提问by john french

I have a field that is varchar(8), holding date values that I converted from floatto varchar.

我有一个字段是varchar(8)，保持日期值，我从转换float到varchar。

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.

有些记录有八个字符，有些记录有七个。我想通过在有 7 的前导零上添加一个前导零，使它们的长度都相同。

• 8 char example: 12162003

• 7 char example: 5072004 (needs a leading zero)

• 8 个字符示例：12162003

• 7 个字符示例：5072004（需要一个前导零）

The query:

查询：

select birthdate_new from table_name 

回答by Lukasz Szozda

You can use RIGHT:

您可以使用RIGHT：

SELECT RIGHT('00000000' + birthdate_new, 8) AS birthdate_new
FROM table_name;

LiveDemo

LiveDemo

If you want to UPDATEfield use:

如果你想UPDATE现场使用：

UPDATE table_name
SET birthdate_new = RIGHT('00000000' + birthdate_new, 8)
WHERE LEN(birthdate_new) < 8;

回答by dfundako

A function that will work for more situations would be REPLICATE. It concatenates a value X amount of times to a string.

适用于更多情况的函数是 REPLICATE。它将值 X 次连接到一个字符串。

SELECT REPLICATE('0', 8-LEN(birthdate_new)) + birthdate_new AS 8_len_birthdate 

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.

这将取您的出生日期的长度，从 8 中减去它，然后将许多前导 0 放在前面以使其成为 8 个字符。

回答by Alex K.

As its always 7/8 you could:

一如既往的 7/8 你可以：

select right('0' + fld, 8)

or

或者

select case when len(fld) = 7 then '0' else '' end + fld