By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable
([ID] varchar(4), [VALUE] varchar(28))
;
INSERT INTO YourTable
([ID], [VALUE])
VALUES
('T123', '1,6,7,9,17,22,43'),
('T456', '2,3,5,7,8,13,28,32,41'),
('T789', '1,2,4,5,6,7,9,11,15,23,34,42')
SELECT [ID],SUBSTRING(padded, 2, LEN(padded)-2)
FROM YourTable
CROSS APPLY (
SELECT
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(
',' + [VALUE] + ','
, ',1,', ',01,'), ',2,', ',02,'), ',3,', ',03,')
, ',4,', ',04,'), ',5,', ',05,'), ',6,', ',06,')
, ',7,', ',07,'), ',8,', ',08,'), ',9,', ',09,')
) CA(padded)
| ID | (No column name) |
|---|---|
| T123 | 01,06,07,09,17,22,43 |
| T456 | 02,03,05,07,08,13,28,32,41 |
| T789 | 01,02,04,05,06,07,09,11,15,23,34,42 |