SQL Server 2022

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

CREATE TABLE mytable (
  CLASS int, 
  VENDOR int,
  STYLE int, 
  COLOR int, 
  SIZE int
);

insert into mytable values
(2,4,1,31,22),
(15,100,10,78,23),
(153,345,187,1,22),
(9000,15400,2420,9,23),
(null,null,null,null,null);
5 rows affected

select format(CLASS,'0000') as CLASS,
       format(VENDOR,'00000') as VENDOR,
       format(STYLE,'0000') as STYLE,
       format(COLOR,'000') as COLOR,
       format(SIZE,'0000') as SIZE
from mytable


CLASS
VENDOR
STYLE
COLOR
SIZE


0002
00004
0001
031
0022

0015
00100
0010
078
0023

0153
00345
0187
001
0022

9000
15400
2420
009
0023

null
null
null
null
null


select format(coalesce(CLASS,0),'0000') as CLASS,
       format(coalesce(VENDOR,0),'00000') as VENDOR,
       format(coalesce(STYLE,0),'0000') as STYLE,
       format(coalesce(COLOR,0),'000') as COLOR,
       format(coalesce(SIZE,0),'0000') as SIZE
from mytable


CLASS
VENDOR
STYLE
COLOR
SIZE


0002
00004
0001
031
0022

0015
00100
0010
078
0023

0153
00345
0187
001
0022

9000
15400
2420
009
0023

0000
00000
0000
000
0000


select format(coalesce(CLASS,0),'0000') +
       format(coalesce(VENDOR,0),'00000') +
       format(coalesce(STYLE,0),'0000') +
       format(coalesce(COLOR,0),'000') +
       format(coalesce(SIZE,0),'0000') as CONCATENAD
from mytable


CONCATENAD


00020000400010310022

00150010000100780023

01530034501870010022

90001540024200090023

00000000000000000000

CLASS	VENDOR	STYLE	COLOR	SIZE
0002	00004	0001	031	0022
0015	00100	0010	078	0023
0153	00345	0187	001	0022
9000	15400	2420	009	0023
null	null	null	null	null