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