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

Here is another more simple version.

SQL

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

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

0 comments

How to find & replace ~ separated value

6 answers

Your answer