Try something like this - I wasn't able to test without setting up data, but this should be close:

SELECT

Field=F.Comments

--find starting location: first colon + 1

,StartAt=CHARINDEX( ':',F.Comments) + 1

--find ending location: first pipe - 1

,EndAt=CHARINDEX( '|',F.Comments) - 1

--find number of chars to return (end location - start location)

,NumberOfChars=(CHARINDEX( '|',F.Comments) - 1) - (CHARINDEX( ':',F.Comments) + 1)

--get substring: substring(field, StartAt, NumberOfChars)

,GetMidString=SUBSTRING(F.Comments,CHARINDEX( ':',F.Comments) + 1, (CHARINDEX( '|',F.Comments) - 1) - (CHARINDEX( ':',F.Comments)))

,GetString=SUBSTRING(F.Comments, 0, CHARINDEX( '|',F.Comments) - 1

--put them together

,CASE

WHEN F.Comments IS NULL THEN NULL

WHEN F.Comments LIKE 'File%' THEN SUBSTRING(F.Comments,CHARINDEX( ':',F.Comments) + 1, (CHARINDEX( '|',F.Comments) - 1) - (CHARINDEX( ':',F.Comments)))

ELSE SUBSTRING(F.Comments, 0, CHARINDEX( '|',F.Comments) - 1

END