SQL Server How to replace data with stuff

Question

This is my string.

declare @grp varchar(max) ='<GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>'

i want to replace text only with in first & second ~ sign. i tried this way but not getting right result.

select stuff(@grp, charindex('~', @grp, 1)+1,charindex('~', @grp, 1)+1,'Hello')

i am getting this result which is wrong.

<GroupKey>Segment Detail~Hello1~CL</GroupKey>
expected result would be
<GroupKey>Segment Detail~Hello1~RD_100~NBM~~1~CL</GroupKey>

please tell me where i made the mistake. thanks

Answer 1

Hi，@T.Zacks
Welcome to Microsoft T-SQL Q&A Forum!

Since you only want to use stuff, jingyangli's is the perfect answer, here's an explanation of your error . I think you know the usage of Stuff, now we will divide your design into several steps.

1) Get the position where the first ~ appears, and the ~ appears at the 26th position.

 declare [@](/users/na/?userId=b5cba8c2-4001-0003-0000-000000000000) varchar(max) ='<GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>'  
select charindex('~', [@](/users/na/?userId=b5cba8c2-4001-0003-0000-000000000000), 1)+1  

2）There are 13 spaces between ~Total Revenue~ , and the third parameter in the stuff you use is the same as the second one , delete the element occupying the 26th position from the 26th position, and insert it after the first ~ appears hello, so there is a problem in this step.

 declare [@](/users/na/?userId=b5cba8c2-4001-0003-0000-000000000000) varchar(max) ='<GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>'  
select charindex('~', stuff([@](/users/na/?userId=b5cba8c2-4001-0003-0000-000000000000),1, charindex('~', [@](/users/na/?userId=b5cba8c2-4001-0003-0000-000000000000))+1,''))  

![

]3
3）Finally, use our stuff function to start from the first ‘~’ position of the string: 26, delete 13 characters, that is, the two ‘~’ Total Revenue (~Total Revenue~) in the middle, and insert the replaced hello.

select  stuff(@grp, charindex('~', @grp)+1,charindex('~', stuff(@grp,1, charindex('~', @grp)+1,'')),'Hello')  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

 declare @grp varchar(max) ='<GroupKey>Segment Detail~Total Revenue~RD_100~NBM~~1~CL</GroupKey>'


 select  stuff(@grp, charindex('~', @grp)+1,charindex('~', stuff(@grp,1, charindex('~', @grp)+1,'')),'Hello1')

 /*

<GroupKey>Segment Detail~Hello1~RD_100~NBM~~1~CL</GroupKey>

 */