WITH Occurrences AS (SELECT Number, ROW_NUMBER() OVER(ORDER BY Number) AS OccurrenceFROM master.dbo.spt_valuesWHERE Number BETWEEN 1 AND LEN(@SearchedStr) AND type='P' AND SUBSTRING(@SearchedStr,Number,LEN(@TargetStr))=@TargetStr)SELECT NumberFROM OccurrencesWHERE Occurrence=@Occurrence

Thank you for your fix code! I was banging my head agains the wall to find out why my stored proc was bugging out sometimes - turns out that one of my functions was using this charindex2 without accounting for looping behavior