I suspect this would be more efficient with a numbers/tally table

declare @TargetStr varchar(8000),

@SearchedStr varchar(8000),

@Occurrence int

set @TargetStr='a'

set @SearchedStr='abbabba'

set @Occurrence=3;

WITH Occurrences AS (

SELECT Number,

ROW_NUMBER() OVER(ORDER BY Number) AS Occurrence

FROM master.dbo.spt_values

WHERE Number BETWEEN 1 AND LEN(@SearchedStr) AND type='P'

AND SUBSTRING(@SearchedStr,Number,LEN(@TargetStr))=@TargetStr)

SELECT Number

FROM Occurrences

WHERE Occurrence=@Occurrence

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537