How to find all positions of a string within another string
I think this will be slightly more efficient than the looping method you've chosen (some evidence here), and definitely more efficient than the recursive 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);
Sample usage:
DECLARE @name NVARCHAR(MAX);
SET @name = N'ali reza dar yek shabe barani ba yek'
+ ' dokhtare khoshkel be disco raft va ali baraye '
+ '1 saat anja bud va sepas... ali...';
SELECT pos FROM dbo.FindPatternLocation(@name, 'ali');
Results:
pos
---
1
74
113
If your strings will be longer than 2K then use sys.all_columns instead of sys.all_objects. If longer than 8K then add a cross join.
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...'
Declare @a table (pos int)
Declare @pos int
Declare @oldpos int
Select @oldpos=0
select @pos=patindex('%ali%',@name)
while @pos > 0 and @oldpos<>@pos
begin
insert into @a Values (@pos)
Select @oldpos=@pos
select @pos=patindex('%ali%',Substring(@name,@pos + 1,len(@name))) + @pos
end
Select * from @a
To make it reuseable you can use it in a table function to call it like:
Select * from dbo.F_CountPats ('ali reza dar yek shabe barani ba yek dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...','%ali%')
The function could look like this
Create FUNCTION [dbo].[F_CountPats]
(
@txt varchar(max),
@Pat varchar(max)
)
RETURNS
@tab TABLE
(
ID int
)
AS
BEGIN
Declare @pos int
Declare @oldpos int
Select @oldpos=0
select @pos=patindex(@pat,@txt)
while @pos > 0 and @oldpos<>@pos
begin
insert into @tab Values (@pos)
Select @oldpos=@pos
select @pos=patindex(@pat,Substring(@txt,@pos + 1,len(@txt))) + @pos
end
RETURN
END
GO
--Recursive CTE
with cte as
(select 'ali reza dar yek shabe barani ba yek dokhtare khoshkel be disco raft va ali baraye 1 saat anja bud va sepas... ali...' as name
),
pos as
(select patindex('%ali%',name) pos, name from cte
union all
select pos+patindex('%ali%',substring(name, pos+1, len(name))) pos, name from pos
where patindex('%ali%',substring(name, pos+1, len(name)))>0
)
select pos from pos