Update in loop SQL

Question

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

it looks like

LP	Text
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'

it should look like this

LP	Text
1	'Mamma mia #XX[01]'
2	'Joe is my best #XX[02] friend'
3	Hi hey haloo'
4	'one #XX[03] two #XX[04] three #XX[05]'
5	'best phone ever'
6	'Nice im love it #XX[06] what we do next #XX[07]'

Have you already made any attempt to solve this problem? – lemon May 20 '23 at 20:22 — lemon, May 20 '23 at 20:22

score 3 · Accepted Answer · answered May 20 '23 at 20:30

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

@KrzysztofJankowicz Neighbor would OpenJSON. Clearly not a solution for you then — John Cappelletti, May 21 '23 at 11:27

score 1 · Answer 2 · answered May 20 '23 at 20:47

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:

LP	Text
1	Mamma mia #XX[01]
2	Joe is my best #XX[02] friend
3	Hi hey haloo
4	one #XX[03] two #XX[04] three #XX[05]
5	best phone ever
6	Nice im love it #XX[06] what we do next #XX[07]

Check the demo here.

You can follow up with [this thread](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017), although string_agg won't be the only cool tool not to be working in that version. I'd recommend to update your SQL Server version if you have the possibility, or go with *very* ugly and expensive queries, way further than this and John's. — lemon, May 21 '23 at 08:17

score -1 · Answer 3 · edited May 23 '23 at 18:02

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

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. **Would you kindly [edit] your answer to include additional details for the benefit of the community?** while you’re at it, please correct the formatting. — Jeremy Caney, May 22 '23 at 17:28

Update in loop SQL

3 Answers3