This function returns position or
index of characters in a string after give number of occurrence (nth) of same character:
CREATE FUNCTION
CharIndexAfterN (
@Char
VARCHAR(10)
,@String VARCHAR(MAX)
,@Occurance INT
)
RETURNS INT
AS
BEGIN
DECLARE @Index AS INT = 1
WHILE @Occurance <>
0
BEGIN
SET @Index = CHARINDEX(@Char, @String, @Index + 1)
SET @Occurance -= 1
END
RETURN @Index
END
For example:
SELECT dbo.CharIndexAfterN('ab', '***ab****ab*******ab*', 2)
Output: 10