How to find & replace ~ separated value

Sudip Bhatt 2,281

say this is my text and multiple value is there separated by ~

SET @oldGroupKeyvalue='Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1'

i want to pick value from 2nd ~ to 3rd ~ and replace by some other value.

How can i easily pick value from second tilde and to third tilde. so desire picked value will be TRIN

and want to replace TRIN with RD_101
please share the code to do it easy way. thanks

MelissaMa-msft 24,246 Reputation points Moderator

2020-12-08T01:34:41.677+00:00

Hi @Sudip Bhatt ,

Could you please validate all the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. Thank you for understanding!

Best regards
Melissa

6 answers

Yitzhak Khabinsky 27,196 Reputation points

2020-12-03T13:35:07.593+00:00
Same as before. XML data model is based on ORDERED sequences. That's why it is so easy to get to the 3rd token.

SQL
```
DECLARE @oldGroupKeyvalue  VARCHAR(MAX) = 'Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1'
 , @NewThirdToken VARCHAR(20) =  'RD_101'
 , @separator CHAR(1) = '~';


SELECT @oldGroupKeyvalue AS [Before]
, REPLACE(TRY_CAST('<root><r>' + 
            REPLACE(@oldGroupKeyvalue, @separator, '</r><r>') + 
            '</r></root>' AS XML).query('<root>
{
   for $x in /root/r
   let $pos := count(/root/r[. << $x[1]]) + 1
   return if ($pos = 3) then <r>{sql:variable("@NewThirdToken")}</r>
   else $x
}
</root>').query('
   for $i in /root/r
   return if ($i is (/root/r[last()])[1]) then string($i)
         else concat($i, sql:variable("@separator"))
').value('.', 'NVARCHAR(MAX)'),@separator + SPACE(1), @separator) AS [After];
```
Was this answer helpful?

1 person found this answer helpful.
Sudip Bhatt 2,281 Reputation points

2020-12-03T16:31:01.813+00:00

Thanks for sharing your code very honestly i do not understand your code how it works. can you break you code and try to explain what it is doing.

How can i debug your code specially this two area.

{
for $x in /root/r
let $pos := count(/root/r[. << $x[1]]) + 1
return if ($pos = 3) then <r>{sql:variable("@NewThirdToken")}</r>
else $x
}

and

</root>').query('
for $i in /root/r
return if ($i is (/root/r[last()])[1]) then string($i)
else concat($i, sql:variable("@separator"))
').value('.', 'NVARCHAR(MAX)'),@separator + SPACE(1), @separator) AS [After];

Yitzhak Khabinsky 27,196 Reputation points

2020-12-03T19:22:59.753+00:00

It is XQuery and its FLWOR expression.
Check it out here: flwor-statement-and-iteration-xquery
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

How to find & replace ~ separated value

6 answers

Your answer