Como encontrar todas as posições de uma string dentro de outra string

Acho que isso será um pouco mais eficiente que o método de loop que você escolheu ( algumas evidências aqui ) e definitivamente mais eficiente que o CTE recursivo:

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);

Exemplo de uso:

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');

Resultados:

pos
---
  1
 74
113

Se suas strings forem maiores que 2K, use sys.all_columns em vez de sys.all_objects. Se for maior que 8K, adicione uma junção cruzada.

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

Para torná-lo reutilizável, você pode usá-lo em uma função de tabela para chamá-lo como:

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%')

A função pode ficar assim

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

--CTE Recursivo

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

Eu amo a resposta de Aaron Bertrand. Embora eu não entenda completamente, parece muito elegante.

No passado, tive problemas com permissões ao usar sys.objects. Combinado com a necessidade de solucionar problemas do código, criei uma variação do código de Aaron e a adicionei abaixo.

Este é o meu procedimento:

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

O MAX(posid)valor também é o número de correspondências encontradas.

Este é um código simples baseado na resposta de Aaron que:

• Não limitado ao tamanho de sys.all_objects
• Não perca o último 'X'

CÓDIGO:

DECLARE @termToFind CHAR(1) = 'X'
DECLARE @string VARCHAR(40) = 'XX XXX  X   XX'

SET @string += '.' --Add any data here (different from the one searched) to get the position of the last character

DECLARE @stringLength BIGINT = len(@string)

SELECT pos = Number - LEN(@termToFind)
FROM (
    SELECT Number
        , Item = LTRIM(RTRIM(SUBSTRING(@string, Number, CHARINDEX(@termToFind, @string + @termToFind, Number) - Number)))
    FROM (
        --All numbers between 1 and the lengh of @string. Better than use sys.all_objects
        SELECT TOP (@stringLength) row_number() OVER (
                ORDER BY t1.number
                ) AS N
        FROM master..spt_values t1
        CROSS JOIN master..spt_values t2
        ) AS n(Number)
    WHERE Number > 1
        AND Number <= CONVERT(INT, LEN(@string))
        AND SUBSTRING(@termToFind + @string, Number, LEN(@termToFind)) = @termToFind
    ) AS y

RESULTADO

pos
--------------------
1
2
4
5
6
9
13
14

(8 row(s) affected)

Desculpe, pessoal, por aparecer tão tarde, mas gostaria de facilitar as coisas para as pessoas que desejam expandir isso. Eu estava olhando cada uma dessas implementações, peguei a que me pareceu melhor (Aaron Bertrand), simplifiquei e aqui está, você tem o "template". Use-o com sabedoria.

CREATE FUNCTION dbo.CHARINDICES (
    @search_expression NVARCHAR(4000),
    @expression_to_be_searched NVARCHAR(MAX)
) RETURNS TABLE AS RETURN (
    WITH tally AS (
        SELECT Number = ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects)
    SELECT DISTINCT n = subIdx -- (4) if we don't perform distinct we'll get result for each searched substring, and we don't want that
    FROM 
        tally 
        CROSS APPLY (SELECT subIdx = CHARINDEX(@search_expression, @expression_to_be_searched, Number)) x -- (2) subIdx is found in the rest of the substring 
    WHERE 
        Number BETWEEN 1 AND LEN(@expression_to_be_searched) -- (1) run for each substring once
        AND SubIdx != 0  -- (3) we care only about the indexes we've found, 0 stands for "not found"
)

SELECT CHARINDEX('C', 'BACBABCBABBCBACBBABC')
SELECT * FROM dbo.CHARINDICES('C', 'BACBABCBABBCBACBBABC')

Apenas como referência - você pode derivar outros comportamentos disso, como expandir em PATINDEX():

CREATE FUNCTION dbo.PATINDICES (
    @search_expression NVARCHAR(4000) = '%[cS]%',
    @expression_to_be_searched NVARCHAR(MAX) = 'W3Schools.com'
) RETURNS TABLE AS RETURN (
    WITH tally AS (
        SELECT num = ROW_NUMBER() OVER (ORDER BY [object_id])
        FROM sys.all_objects)
    SELECT DISTINCT n = subIdx + num - 1
    FROM 
        tally 
        CROSS APPLY (SELECT numRev = LEN(@expression_to_be_searched) - num + 1) x
        CROSS APPLY (SELECT subExp = RIGHT(@expression_to_be_searched, numRev)) y
        CROSS APPLY (SELECT subIdx = PATINDEX(@search_expression, subExp)) z
    WHERE 
        num BETWEEN 1 AND LEN(@expression_to_be_searched)
        AND SubIdx != 0
)

SELECT PATINDEX('%[cS]%', 'W3Schools.com')
SELECT * FROM dbo.PATINDICES('%[cS]%', 'W3Schools.com')

Declare @search varchar(5)
    sET @search='a'
    Declare @name varchar(40)
    Set @name='AmitabhBachan'
    Declare @init int
    Set @init=1
    Declare @hold int
    Declare @table table (POSITION Int)
    While( @init<= LEn(@name))
    Begin
   Set @hold=(Select CHARINDEX(@search,@name,@init))
   If (@hold!=0)
   BEgin 
   --Print @hold
   Insert into @table
   Select @hold
   Set @init=@hold+1
   End 
   Else
   If (@hold=0)
   BEgin
   Break
   End
  End
  Select * from @table