kpitsimpl: CTE

CREATE FUNCTION dbo.FindPatternLocation
(
    @string NVARCHAR(MAX),
    @term   NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN 
    (
      SELECT pos = Number - LEN(@term) 
      FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@string, Number, 
      CHARINDEX(@term, @string + @term, Number) - Number)))
      FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_objects) AS n(Number)
      WHERE Number > 1 AND Number <= CONVERT(INT, LEN(@string)+1)
      AND SUBSTRING(@term + @string, Number, LEN(@term)) = @term
    ) AS y);

Simply use the function above.

If you want to understand technically how this works, see the reference below.

Essentially, this function dynamically creates a table (CTE) of the term being searched for with Number being the Row_Number of each charindex within the string.

This CTE is then searched to elicit the starting positions of each location where string term matches ([WHERE] SUBSTRING(@term + @string, Number, LEN(@term)) = @term).

Usage:

The part: "SELECT ROW_NUMBER() OVER (ORDER BY [object_id].." is there to provide space to establish the CTE with which to store search string indexes. If you need more space to traverse over (your search string is > 2k characters) you can use sys.all_columns instead of sys.all_objects or a CROSS JOIN for tables with greater than 8k records.

Reference: https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

All credits: Aaron Bertrand. He is a T-SQL genius. If it can be done in T-SQL, Aaron has done it, benchmarked it and probably blogged about it: https://sqlperformance.com/author/abertrand

Find all instances of a string within a string in T-SQL