I would say that if the functionality is sufficient, then use the charindex.
If you do:
DECLARE @sum FLOAT, @S VARCHAR(255)
SELECT @S = 'foobar', @sum = 0.0
WHILE @sum < 1000000 BEGIN
SELECT @sum = @sum + CHARINDEX('o', @S)
END
SELECT @sum
and
DECLARE @sum FLOAT, @S VARCHAR(255)
SELECT @S = 'foobar', @sum = 0.0
WHILE @sum < 1000000 BEGIN
SELECT @sum = @sum + PATINDEX('%o%', @S)
END
SELECT @sum
there is no real performance difference.