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

>Solution :

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