SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test_1(id int, gpf4 numeric(18,0));

insert into test_1 values
(1,1234567890),
(2,1234567),
(3,123456789);

select * from test_1;


id
gpf4


1
1234567890

2
1234567

3
123456789


select id,gpf4,len(gpf4) as leng from test_1;


id
gpf4
leng


1
1234567890
10

2
1234567
7

3
123456789
9


select id,gpf4,len(gpf4) as leng, replicate('0',10-len(gpf4)) as rep0 from test_1;


id
gpf4
leng
rep0


1
1234567890
10


2
1234567
7
000

3
123456789
9
0


with temp_1 as
(select id,gpf4,replicate('0',10-len(gpf4)) as rep0 from test_1)

select *,convert(varchar(10),rep0)+convert(varchar(10),gpf4) as new_gpf4 from temp_1;


id
gpf4
rep0
new_gpf4


1
1234567890

1234567890

2
1234567
000
0001234567

3
123456789
0
0123456789

id	gpf4	rep0	new_gpf4
1	1234567890		1234567890
2	1234567	000	0001234567
3	123456789	0	0123456789