Return to Revisions

I love the answer of Aron Bertrand, although I don't understand it completely, it looks really elegant.

I needed something, I would understand completely, so that I could troubleshoot it myself, and that wouldn't use sys.objects, because I experienced permission issues with this in the past. So put it into a stored procedure that would only need the two strings, the string to search and the string to find in it.

This is my procedure:

CREATE PROCEDURE dbo.FindPatternLocations
-- Params
@TextToSearch nvarchar (max),
@TextToFind nvarchar (255)

AS
BEGIN
   
    declare @Length int
    set @Length = (Select LEN(@TextToSearch))
   
    declare @LengthSearchString int
    set @LengthSearchString = (select LEN (@TextToFind))

    declare @Index int
    set @Index=1
    
    create table #Positions (
    [POSID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    POS int
    )
    
        set @Index = (select charindex(@TextToFind, @TextToSearch, @Index))
        insert into #Positions (POS) select @Index

        if @Index = 0 goto Ende -- TextToFind is not in TextToSearch
        set @Index = @Index + @LengthSearchString

while @Index <= @Length - @LengthSearchString   
    Begin
            set @Index = (select charindex(@TextToFind, @TextToSearch, @Index) )
            if @Index = 0 goto Ende -- no findings anymore
            insert into #Positions (POS) select @Index
            set @Index = @Index + @LengthSearchString
    end
Ende:
select * from #Positions
END
GO