Stuck to understand a code snippet where many charindex has been used to find & replace text

Question

The below code find text by this name Segment Detail. if found then change the old xfundcode with new xfundcode. full code given. so many charindex which create confusion for me. please some one help me to understand mention below area with easy example. code is perfectly working.

Specially this area not clear
stuff(anonymous userGroupKeyvalue,
--starting from position of 2nd ~ +1
charindex('~', anonymous userGroupKeyvalue, charindex('~', anonymous userGroupKeyvalue, 1)+1)+1,
--as many characters as exist between 3rd ~ and 2nd ~ (excluding ~)
charindex('~', anonymous userGroupKeyvalue, charindex('~', anonymous userGroupKeyvalue, charindex('~', anonymous userGroupKeyvalue, 1)+1)+1)-1-
charindex('~', anonymous userGroupKeyvalue, charindex('~', anonymous userGroupKeyvalue, 1)+1),
--stuff the new xfundcode
@newXfundCode
);

DECLARE @xml xml
 DECLARE @li varchar(max) ='Segment Detail'
 DECLARE @newXfundCode varchar(max) ='TEST'

 CREATE TABLE #tmpData (id INT, xmldata xml)  

 INSERT INTO #tmpData (id, xmldata) 
 VALUES (1,N'<?xml version="1.0" encoding="utf-16"?>
 <PWR_ViewAll>
 <dgvViewAll_Vertical>
   <Section_x0020_>ZB-P1</Section_x0020_>
   <LineItem>B. Riley FBR Inc.</LineItem>
   <Revise_x0020_Date>08-21-2020</Revise_x0020_Date>
   <GroupKey>Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1</GroupKey>
 </dgvViewAll_Vertical>
 <dgvViewAll_Vertical>
   <Section_x0020_>CL</Section_x0020_>
   <LineItem>Deutsche Bank</LineItem>
   <Revise_x0020_Date>02-28-2020</Revise_x0020_Date>
   <GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>
 </dgvViewAll_Vertical>
 </PWR_ViewAll>')

 SELECT @xml=xmldata from #tmpData where ID=1

 declare 
     *anonymous user*GroupKeyvalue varchar(100), 
     @newGroupKeyValue varchar(100);

 SELECT *anonymous user*GroupKeyvalue = col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)')
 FROM @xml.nodes('/PWR_ViewAll/dgvViewAll_Vertical') AS tab (col)
 WHERE  CHARINDEX(@li, col.value('(GroupKey/text())[1]', 'VARCHAR(MAX)'))>0;


 --@newGroupKeyValue = *anonymous user*groupkeyvalue but stuff/"replace" string between 2nd&3rd ~ with @newXfundCode
 select  @newGroupKeyValue = 
 --"replace/stuff" the old xfundcode := all chars from position of 2nd ~ (+1) till position of the 2nd ~ (minus 1) with an empty string
 stuff(*anonymous user*GroupKeyvalue, 
 --starting from position of 2nd ~ +1
 charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1)+1,
 --as many characters as exist between 3rd ~ and 2nd ~ (excluding ~)
 charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1)+1)-1-
 charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1),
 --stuff the new xfundcode
 @newXfundCode
 );

 --test
 select *anonymous user*GroupKeyValue as oldgroupkeyvalue, @newGroupKeyValue as newgroupkeyvalue;


 --test
 select @xml as oldxml;

 --modify xml, replace the text of GroupKey element whose text equals *anonymous user*GroupKeyValue with @newGroupKeyValue
 --if newGroupKeyValue is not null etc...
 set @xml.modify('
   replace value of (/PWR_ViewAll/dgvViewAll_Vertical/GroupKey[.=sql:variable("*anonymous user*GroupKeyValue")]/text())[1]  
   with sql:variable("@newGroupKeyValue") 
 '
 );

 select @xml as newxml;


 --update #tmpData set xmldata = @xml where ID=1;
 --select * from #tmpData;

 Drop table #tmpData

Answer 1

Hi @Sudip Bhatt ,

The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.It returns a number.

The syntax of stuff：

    STUFF ( character_expression , start , length , replaceWith_expression )  

The value returned by the statement is the value of the start variable：

  charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1)+1  

The value returned by the statement is the value of the length variable：

  charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1)+1)-1-  
  charindex('~', *anonymous user*GroupKeyvalue, charindex('~', *anonymous user*GroupKeyvalue, 1)+1)  

Generally speaking, for the complex form of this simple function, you only need to know the most basic syntax of the function, and then find the expression corresponding to the position of the parameter, and you can understand it layer by layer. You will find that those complicated forms are actually just illusions.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table