Adding a leading zero to single digits in a comma separated string of numbers

Question

I have a table with two columns. ID and VALUE. The VALUE column contains a combination of a string of numbers from 1 to 45. EG :-

ID	VALUE
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

I'm trying to report out the values column for comparing against similar data from another source but the numbers in my soucre need to be changed so that all numbers are two digits. So any number between 1 and 9 starts with a zero so :-

ID	VALUE
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

I thought I'd come up with an answer by breaking up the string, checking the length of each number, adding a zero if required then building the string back up. But I couldn't work out how to apply the loop to each row returned.

This data is in a SQL Server 2014 database and it can't be changed in the source. I did wonder if somehow changing the data and loading into a temp table then selecting the results from there might work but again, I'm not sure of the best way to step through the string and add a zero to any single digit numbers.

Would anyone be able to help or have any suggestions?

Thank you.

Answer 1

You could split the string apart and rejoin it but working on SQL Server 2014 you are missing some useful functions for both parts of that. I might just do some nested REPLACE functions (Fiddle).

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)

• UK1539: That's worked. Thank you for responding so quickly.