question

This approach uses a number of useful techniques. First we are going to use a tally table to split the comma separated string into each value - we treat these as integers so any leading '0' is stripped off. This will give us 26 values, and using row_number() function we can allocate a value 1-26 to each row. Add that value to 64 and you get the ASCII values A-Z. If you change the last query to `select * from CodedItems` you 'll see the values that you end up with. Finally use the 'xml concatenation' trick to bundle it all back together. declare @BOM_Item varchar(max) set @BOM_Item = '02,03,01,02,01,,,01,,,,,,,,,,,,,,,,,,1'; set @BOM_Item = ','+@BOM_Item+',' --first split the string in to proper data, i.e. a column ;WITH Nbrs_4( n ) AS ( SELECT 1 UNION SELECT 0 ), --2 Nbrs_3( n ) AS ( SELECT 1 FROM Nbrs_4 n1 CROSS JOIN Nbrs_4 n2 ), --2*2 = 4 Nbrs_2( n ) AS ( SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), --4*4 = 16 Nbrs_1( n ) AS ( SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), --16*16 = 256 Nbrs_0( n ) AS ( SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), --256*256 = 65,536 Nbrs ( n ) AS ( SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) --65536*65536 = 4,294,967,296 , tally_cte as ( select n FROM ( SELECT ROW_NUMBER() OVER (ORDER BY n) FROM Nbrs ) D ( n ) where n < 500 ) , Items as ( select row_number()over(order by N) as posn, cast(substring(@BOM_Item,N+1,charindex(',',@BOM_Item,N+1)-N-1) as int) as Value from tally_cte where N < len(@BOM_Item) and substring(@BOM_Item,N,1) = ',' ) , CodedItems as ( select char(64+posn) + cast(value as varchar) as LetterNumber from Items where value 0 ) -- select * from CodedItems select (SELECT '' + isnull(LetterNumber , ' ') FROM CodedItems for xml path(''), type ).value('.','varchar(max)') returns ------------------------------ A2B3C1D2E1H1Z1 (1 row(s) affected)