Update in loop SQL

I am supposed to make a loop in which I will update the tag and add the registration number to it

it looks like

it should look like this

A bit ugly, but loops should be the last resort.

Example

Declare @YourTable Table ([LP] int,[Text] varchar(50))  Insert Into @YourTable Values 
 (1,'Mamma mia #XX')
,(2,'Joe is my best #XX friend')
,(3,'Hi hey haloo')
,(4,'one #XX two #XX three #XX')
,(5,'best phone ever')
,(6,'Nice im love it #XX what we do next #XX')

;with cte as (
Select * 
      ,Seq = sum(case when RetVal like '#%' then 1 else 0 end) over ( order by RN,RetSeq)
      ,Pos = charindex(' ',RetVal+' ')
 From  (
		Select *
			  ,RN = row_number() over( order by LP) 
		 from @YourTable
       ) A 
 Cross Apply (
            Select RetSeq = [Key]+1
                  ,RetVal = trim(Value)
             From  OpenJSON( '["'+replace(string_escape(replace([Text],'#','|||#'),'json'),'|||','","')+'"]' )

             ) B
)
Select LP
      ,NewVal = string_agg(
	            case when RetVal not like '#%' then RetVal
	                 else stuff(RetVal+' ',Pos,0,concat('[',format(Seq,'00'),'] '))
				end
				,' ') WITHIN GROUP (ORDER BY RN,Seq)
 From  cte
 Group By LP
 Order By LP

Results

Another approach could:

• split your strings on # characters,
• compute the amount of times "#XX" is present, with a running sum
• replace "#XX" with "#XX[n]"
• aggregate your string parts again

WITH cte AS (
    SELECT LP,
           REPLACE(
               IIF(LEFT(value, 3) = 'XX ', CONCAT('#',value), value),
               '#XX', 
               CONCAT(
                   '#XX[',
                   FORMAT(SUM(IIF(LEFT(value, 3) = 'XX ', 1, 0)) OVER(ORDER BY LP, ordinal), '00'),
                   ']'
               )
           ) AS [TextParts]
    FROM tab
    CROSS APPLY STRING_SPLIT(tab.[Text], '#', 1)
)
SELECT LP, 
       STRING_AGG([TextParts], '') AS [Text]
FROM cte
GROUP BY LP

Output:

Check the demo here.

Declare @TempTable Table ([LP] int,[Text] varchar(50))  Insert Into @TempTable Values 
 (1,'Mamma mia #XX')
,(2,'Joe is my best #XX friend')
,(3,'Hi hey haloo')
,(4,'one #XX two #XX three #XX')
,(5,'best phone ever')
,(6,'Nice im love it #XX what we do next #XX')

Declare @TempTable2 Table ([LP] int,[Text] varchar(50))

DECLARE 
    @lp INT, 
    @text   VARCHAR(MAX);


select * from @TempTable

Declare cursor_temptable cursor for select LP,Text from @TempTable
open cursor_temptable 

Declare @myPrevValue int=0
Declare @myNextValue int=0


FETCH NEXT FROM cursor_temptable INTO @lp,@text
while @@FETCH_STATUS=0
Begin

declare @count int
set @count=(select count(*)-1 from STRING_SPLIT(@text,'#'))
set @myNextValue=@myPrevValue+@count

insert into @TempTable2 values(@lp,(select 
STRING_AGG(a.value,'#')
From(
select replace(value,'XX','XX'+'[0' +convert(varchar,(rank() over(order by value)-1+@myPrevValue)) +'] ' ) value from STRING_SPLIT(@text,'#')
) as a))


FETCH NEXT FROM cursor_temptable INTO @lp,@text
set @myPrevValue=@myNextValue
end

close cursor_temptable
deallocate cursor_temptable


select * from @TempTable2