Maybe if you're trying to do all this to a table, the cCA method might improve performance a bit because you can then take advantage of the starting position parameter of CHARINDEX and also DRY the code out a bit.

--DROP TABLE #MyHead

;

SELECT d.RowNum,d.SomeString

INTO #MyHead

FROM (

SELECT 1,'C1 User Unable to Log in C2 User did not set password Correctly C3 Assisted User with correct password set up' UNION ALL

SELECT 2,'C1 Aye C2 Bee C3 See' UNION ALL

SELECT 3,'C1 One C2 Two C3 Three'

)d(RowNum,SomeString)

;

SELECT RowNum

,F1 = SUBSTRING(SomeString, ca1.F1Start+3, ca2.F2Start-ca1.F1Start-4)

,F2 = SUBSTRING(SomeString, ca2.F2Start+3, ca3.F3Start-ca2.F2Start-4)

,F3 = SUBSTRING(SomeString, ca3.F3Start+3, 8000)

FROM #MyHead t

CROSS APPLY (SELECT CHARINDEX('C1',SomeString )) ca1 (F1Start)

CROSS APPLY (SELECT CHARINDEX('C2',SomeString,cA1.F1Start)) ca2 (F2Start)

CROSS APPLY (SELECT CHARINDEX('C3',SomeString,cA2.F2Start)) ca3 (F3Start)

;

It's not my original idea, though. I got it from Eirikur Erikson. Gotta give the man some credit here.