Weird result of CHARINDEX of SQL SERVER

I am using SQL Server 2014. I have the following statement:

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

The result I get is 3 instead of 8. I observe the same behavior with different strings. Is this expected behavior of this function?

It seems fine if the last parameter is any other number.

Thanks

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!

StackExchange Database Administrators Q#174318, answer score: 9