Since we are going to use PARSENAME to process/parse the string, and it works with a maximum of four parts in the string, we need to create columns to store at most four parts. Then we can use PARSENAME against the column to move the fruit names into columns that have one fruit per column. That’s the objective.

There is another way to do this. We could just use SUBSTRING and CHARINDEX, but as the columns get longer and longer, those expressions get more and more complicated. I find it easier to break them into groups of four and use PARSENAME because PARSENAME is fairly simple to use.

By way of review, here is the code to add the three columns.

ALTER TABLE ParseFruit
    ADD Group1 NVARCHAR(50) NULL, Group2 NVARCHAR(50) NULL, Group3 NVARCHAR(50) NULL
GO

Below is the code to update.

   -- 1 to 4 parts - move it all
  UPDATE ParseFruit
  SET Group1 = ListFruit
  WHERE FruitFreq < 5
-- =======================================================================
 -- 5 to 8
 -- starting position is one after the 4th comma
 -- the length is the total length less the first 4 parts
 UPDATE ParseFruit
 SET Group2 = SUBSTRING(ListFruit, dbo.CHARINDEX2(',', ListFruit, 4) + 1, LEN(ListFruit) - dbo.CHARINDEX2(',', ListFruit, 4))
 WHERE (FruitFreq >= 5 AND FruitFreq  < 9)


 UPDATE ParseFruit
 SET Group1 = SUBSTRING(ListFruit, 1, dbo.CHARINDEX2(',', ListFruit, 4) - 1)
 WHERE (FruitFreq >= 5 AND FruitFreq  < 9)
-- ======================================================================
 -- 9 to 12
 -- starting position is one after the 4th comma
 -- the length is the total length less the first 4 parts
 UPDATE ParseFruit
 SET Group3 = SUBSTRING(ListFruit, dbo.CHARINDEX2(',', ListFruit, 8) + 1, LEN(ListFruit) - dbo.CHARINDEX2(',', ListFruit, 8))
 WHERE (FruitFreq >= 9 AND FruitFreq  <= 12)

-- Group2
UPDATE ParseFruit
SET Group2 = SUBSTRING(ListFruit, dbo.CHARINDEX2(',', ListFruit, 4) + 1, dbo.CHARINDEX2(',', ListFruit, 8) - dbo.CHARINDEX2(',', ListFruit, 4) - 1)
WHERE (FruitFreq >= 9 AND FruitFreq  <= 12)

-- Group1
 UPDATE ParseFruit
 SET Group1 = SUBSTRING(ListFruit, 1, dbo.CHARINDEX2(',', ListFruit, 4) - 1)
 WHERE (FruitFreq >= 9 AND FruitFreq  <= 12)

We’ve got our groups populated now. Next we’ll use PARSENAME to split the groups one by one.