Weird result of CHARINDEX of SQL SERVER
That's not weird at all, that's how CHARINDEX works. The third parameter you pass in is the start position to start searching for your expression.

You'll have to deal with this a lot if you're using CHARINDEX along with SUBSTRING to find text in between two expressions.

If you start searching AT the CHARINDEX of C in your example, you get the same results.

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC'));


Returns 3

You need to start searching for the CHARINDEX one position after to get the next one. Otherwise, it finds the searched expression in the first position it checks.

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC') + LEN('C'));


Returns 8

If you start searching after the last occurrence, CHARINDEX will wrap back around. 

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC', 9));


Also returns 3.

Hope this helps!