Hello,

you can do it in an iterative way:

Find the first colon, keep the left half into Col1 and move the right half into Col2.

Find the first colon in Col2, keep the left half into Col2 and move the right half into Col3.

Find the first colon in Col3, keep the left half into Col3 and move the right half into Col4.

...

You simply should copy and slightly modify an UPDATE statement 25 times.

I coded it in this way:

CREATE TABLE #T (Col1 VARCHAR(200)

, Col2 VARCHAR(200)

, Col3 VARCHAR(200)

, Col4 VARCHAR(200))

INSERT INTO #T (Col1) VALUES ('Apple, Pear, Orange')

INSERT INTO #T (Col1) VALUES ('Pear, Apple, Bananna')

INSERT INTO #T (Col1) VALUES ('Pear, Apple, Bananna, Lemon')

INSERT INTO #T (Col1) VALUES ('Pear, Apple')

INSERT INTO #T (Col1) VALUES ('Kiwi')

GO

SELECT * FROM #T

UPDATE #T

SET Col1 = LEFT(Col1, CHARINDEX(',', Col1) - 1)

, Col2 = LTRIM(SUBSTRING(Col1, CHARINDEX(',', Col1) + 1, LEN(Col1)))

WHERE CHARINDEX(',', Col1) > 0

UPDATE #T

SET Col2 = LEFT(Col2, CHARINDEX(',', Col2) - 1)

, Col3 = LTRIM(SUBSTRING(Col2, CHARINDEX(',', Col2) + 1, LEN(Col2)))

WHERE CHARINDEX(',', Col2) > 0

UPDATE #T

SET Col3 = LEFT(Col3, CHARINDEX(',', Col3) - 1)

, Col4 = LTRIM(SUBSTRING(Col3, CHARINDEX(',', Col3) + 1, LEN(Col3)))

WHERE CHARINDEX(',', Col3) > 0

SELECT * FROM #T

GO

DROP TABLE #T

GO

Hope this helps,

Francesc