How to add leading zeros to exist id

Question

Need to add leading zeros to the id based on the max id.

Sample data:

create table test2 (id int,val varchar(20))
insert into test2 values(1,'aaa'),(11,'bbb'),(111,'ccc')
select * from test2

Result:

Answer 1

Hi @Riley

If I understand right, you could use format like this:

SELECT T.*,FORMAT(id,REPLICATE('0',LEN(max_id))) AS new_id
FROM test2 T CROSS APPLY (SELECT MAX(id)AS max_id FROM test2)C

Best regards,

Cosmog Hong

Answer 2

Numeric values never have leading zero, in no system. You have to convert it to a string, add some "0" at the front and take the right part in length as you want to have it, e.g. 6 :

declare @test2 table (id int,val varchar(20))
insert into @test2 values(1,'aaa'),(11,'bbb'),(111,'ccc')

select t.*, right('000000' + CONVERT(varchar(9), t.id), 6) as new_id
from @test2 as t