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(0,1) NOT FOR REPLICATION NOT NULL,
    POS int
    )
    
    insert into #Positions (POS) select 0 -- to return a row even if no findings occur
    
        set @Index = (select charindex(@TextToFind, @TextToSearch, @Index))
                    if @Index = 0 goto Ende -- TextToFind is not in TextToSearch

        insert into #Positions (POS) select @Index


        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:
if (select MAX(posid) from #Positions) > 0 delete from #Positions where POSID = 0 -- row is not needed if TextToFind occurs
select * from #Positions
END
GO

The max value of POSID is also number of findings.