Add leading zeros to a varchar field

Question

I have a field that is varchar(8), holding date values that I converted from float to 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.

• 8 char example: 12162003

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

The query:

select birthdate_new from table_name 

Answer 1

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 

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.

Answer 2

You can use RIGHT:

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

LiveDemo

If you want to UPDATE field use:

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

Answer 3

As its always 7/8 you could:

select right('0' + fld, 8)

or

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