Split & Pre-Pend Values To Comma Delimited String

1

Here’s what i’m trying to do! Working with a 26 comma separated value string. Trying to build our part # configuration from this.

Database Field BOM_Item
02,03,01,02,01,,,01,,,,,,,,,,,,,,,,,,1

There are 26 sections of comma delimited values
Each is assigned a letter A-Z, alphabetically
So, it reads, A2,B3,C1,D2,E1,H1,Z1
Final Part # Should Read A2B3C1D2E1H1Z1
Just works from left to right, removing the extra zero (could have 2 character number)

Can someone help me add a column to this table, that would take the comma separated string and designate the letters and put it with the number?

I’m pretty savvy, but not a DBA - just hoping to add a column to my database view.

Much thanks!

-David

2

I'm sure there's an even slicker way to do this, but of the top of my head this should work. You can Google the source for function "DelimitedSplit8K", you'll need to create that function first.

SELECT *
FROM (
    VALUES('02,03,01,02,01,,,01,,,,,,,,,,,,,,,,,,1')
) AS test_data(BOM_Item)
CROSS APPLY (
    SELECT 
        MAX(CASE WHEN ItemNumber =  1 AND Item > '' THEN 'A' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber =  2 AND Item > '' THEN 'B' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber =  3 AND Item > '' THEN 'C' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber =  4 AND Item > '' THEN 'D' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber =  5 AND Item > '' THEN 'E' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber =  6 AND Item > '' THEN 'F' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber =  7 AND Item > '' THEN 'G' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber =  8 AND Item > '' THEN 'H' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber =  9 AND Item > '' THEN 'I' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 10 AND Item > '' THEN 'J' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 11 AND Item > '' THEN 'K' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 12 AND Item > '' THEN 'L' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 13 AND Item > '' THEN 'M' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 14 AND Item > '' THEN 'N' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 15 AND Item > '' THEN 'O' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 16 AND Item > '' THEN 'P' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 17 AND Item > '' THEN 'Q' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 18 AND Item > '' THEN 'R' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 19 AND Item > '' THEN 'S' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 20 AND Item > '' THEN 'T' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 21 AND Item > '' THEN 'U' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 22 AND Item > '' THEN 'V' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 23 AND Item > '' THEN 'W' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 24 AND Item > '' THEN 'X' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 25 AND Item > '' THEN 'Y' + RIGHT(Item, 1) ELSE '' END) +
        MAX(CASE WHEN ItemNumber = 26 AND Item > '' THEN 'Z' + RIGHT(Item, 1) ELSE '' END) AS BOL_Items
    FROM dbo.DelimitedSplit8K(BOM_Item, ',')
) AS ca1
3

I'm thinking something like this:

 Select *
   From (
 Values ('02,03,01,02,01,,,01,,,,,,,,,,,,,,,,,,1')
        ) As test_data(BOM_Item)
  Cross Apply (Select stuff((Select char(64 + dsk.ItemNumber) + replace(dsk.Item, '0', '')
                               From dbo.DelimitedSplit8K(BOM_Item, ',') dsk
                              Where Item <> ''
                                For xml Path('')), 1, 0, '')) As c(a)

Could be extended to show upper/lower case letters as needed.