I love theAaron Bertrand's answer of Aron Bertrand, although. 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 inIn the past. So put it I've ran into a stored procedure that would onlyproblems with permissions when using sys.objects. Combined with the need for me to troubleshoot the two stringscode, the string to searchI've come up with a variation on Aaron's code, and the string to find inadded it below.
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 maxMAX(posid) value of POSID is also the number of findingsmatches found.
