t-sql finding a string and reporting the index

Question

Given the following string called @ToSearch how would I get the result matching the index of all the S characters in the string. For example:

set @ToSearch = 'SQLServer 2008 includes a variety of string and null functions' ;
set @Tofind = 's';

match 1       match 2   match 3       match 4
----------- ----------- ----------- -----------
1              4              23       38

I tried this but is not working like I suspected.

SET @ToSearchTemp = 
    SUBSTRING(@ToSearchTemp, CHARINDEX('s',@ToSearchTemp,1), LEN(@ToSearchTemp) );

Any ideas how to solve this with CTE or simply with query.

Answer 1

If you create an auxiliary numbers table first you can then do

SELECT number 
FROM numbers
WHERE number <= LEN(@ToSearch) AND
SUBSTRING(@ToSearch,number,LEN(@Tofind)) = @Tofind

This will give you the result in row format. If you really need in column format you would need to PIVOT but this would require you to either define a maximum number of results or use dynamic SQL.

Answer 2

ALTER PROCEDURE StringSearch 
--(
 @ToSearch VARCHAR(MAX)
,@Tofind   CHAR(1)
--)
AS 

DECLARE @Sentinel INT;
SET @Sentinel =0;
DECLARE @ToSearchTemp VARCHAR(MAX)
SET @ToSearchTemp = @ToSearch
DECLARE @Record int
SET @Record=0;
DECLARE @TestZero int

DECLARE @Matches Table(Match1 int,Id int)
While @Sentinel < LEN(@ToSearch)
    Begin
        INSERT INTO @Matches(Match1,Id)
        VALUES(charindex(@Tofind,@ToSearchTemp)+@Record,@Sentinel+1)
        set @ToSearchTemp = substring(@ToSearchTemp,charindex(@Tofind,@ToSearchTemp)+1,len(@ToSearchTemp))
        print @ToSearchTemp
        SET @TestZero=charindex(@Tofind,@ToSearchTemp)
        if @TestZero=0
        break;
        else
        set @Record =charindex(@Tofind,@ToSearchTemp)+@Record
        SET @Sentinel= @Sentinel +1
    End 

select [1] as Match1, [2] as Match2, [3] as Match3,[4] as Match4

FROM
(
 select top 4  Match1,ID from @Matches
)DataTable
 PIVOT
 (
    sum(Match1)
    For ID
    in ([1],[2],[3],[4])
 ) PivotTable