How to find & replace ~ separated value

Hi @Sudip Bhatt ,

Please also refer below function which could pick value from specific postion and check whether it is also helpful to you.

CREATE FUNCTION dbo.GetSplitString  
(  
   @List       VARCHAR(MAX),  
   @Delimiter  VARCHAR(255),  
   @ElementNumber int  
)  
RETURNS VARCHAR(4000)  
AS  
BEGIN  
   DECLARE @result varchar(4000)      
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,  
                          Item VARCHAR(4000))   
  
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);    
   WITH a AS  
   (  
       SELECT  
           [start] = 1,  
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, @ld), 0), @ll),  
           [value] = SUBSTRING(@List, 1,   
                     COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, @ld), 0), @ll) - 1)  
       UNION ALL  
       SELECT  
           [start] = CONVERT(INT, [end]) + @ld,  
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, [end] + @ld), 0), @ll),  
           [value] = SUBSTRING(@List, [end] + @ld,   
                     COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)  
       FROM a  
       WHERE [end] < @ll  
   )  
  
   INSERT @Items SELECT [value]  
   FROM a  
   WHERE LEN([value]) > 0  
   OPTION (MAXRECURSION 0);  
  
   SELECT @result=Item  
   FROM @Items  
   WHERE position=@ElementNumber  
  
   RETURN @result;  
END  
GO  

Call this function as below:

 DECLARE @oldGroupKeyValue nvarchar(MAX) = 'Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1'  
      
 SET @oldGroupKeyValue = replace(@oldGroupKeyValue,dbo.GetSplitString(@oldGroupKeyValue, '~',3),'RD_101')  
      
 SELECT @oldGroupKeyValue  

Output:

Consensus Model~Total Revenue~RD_101~NBM~~1~ZB-P1  

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
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

I am not sure that this solution is better than any of the others, but it may be easier to grasp and work with. The idea is: use a string-splitter to crack the list into table format, replace the value you want to replace and the create a new separated list.

Since the order matters, we cannot use string_split. Here is a simple-minded string-splitter that gives you the list position:

CREATE FUNCTION iter_charlist_to_tbl
                 (@list      nvarchar(MAX),
                  @delimiter nchar(1) = N',')
      RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          str     varchar(4000)      NOT NULL,
                          nstr    nvarchar(2000)     NOT NULL) AS

BEGIN
   DECLARE @endpos   int,
           @startpos int,
           @textpos  int,
           @chunklen smallint,
           @tmpstr   nvarchar(4000),
           @leftover nvarchar(4000),
           @tmpval   nvarchar(4000)

   SET @textpos = 1
   SET @leftover = ''
   WHILE @textpos <= datalength(@list) / 2
   BEGIN
      SET @chunklen = 4000 - datalength(@leftover) / 2
      SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
      SET @textpos = @textpos + @chunklen

      SET @startpos = 0
      SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2, @tmpstr)

      WHILE @endpos > 0
      BEGIN
         SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
                                             @endpos - @startpos - 1)))
         INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
         SET @startpos = @endpos
         SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2,
                                 @tmpstr, @startpos + 1)
      END

      SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   END

   INSERT @tbl(str, nstr)
      VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
   RETURN
END

And here is the solution itself:

DECLARE @oldGroupKeyValue nvarchar(MAX) = 'Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1'

SELECT @oldGroupKeyValue = string_agg(CASE WHEN listpos = 3 THEN 'RD_101' ELSE nstr END, '~') WITHIN GROUP (ORDER BY listpos)
FROM   iter_charlist_to_tbl(@oldGroupKeyValue, '~')

SELECT @oldGroupKeyValue

If you like CHARINDEX, then try something like this too:

declare @oldGroupKeyvalue varchar(max) = 'Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1'

declare @value varchar(max) = null
declare @newGroupKeyvalue varchar(max) = null

select 
    @value = substring(s, t2.i + 1, t3.i - t2.i - 1), 
    @newGroupKeyvalue = left(s, t2.i) + 'RD_101' + right(s, len(s) - t3.i + 1)
from (values (@oldGroupKeyvalue)) t(s)
cross apply (values (charindex('~', s))) t1(i)
cross apply (values (charindex('~', s, t1.i + 1))) t2(i)
cross apply (values (charindex('~', s, t2.i + 1))) t3(i)

select @oldGroupKeyvalue, @newGroupKeyvalue, @value

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];

I understand how the below code is working which i got few weeks back. here i am sharing that one.

I understand how the below code is working which i got few weeks back. here i am sharing that one.

    DECLARE @newXfundCode varchar(max) ='TEST'
    Declare @oldGroupKeyvalue VARCHAR(200)
    SET @oldGroupKeyvalue='Consensus Model~Total Revenue~TRIN~NBM~~1~ZB-P1'

    DECLARE @FirstPos INT = 0
    DECLARE @LastPos INT = 0

    SET @FirstPos = charindex('~', @oldGroupKeyvalue, charindex('~', @oldGroupKeyvalue)+1)+1
    SET @LastPos = charindex('~', @oldGroupKeyvalue, charindex('~', @oldGroupKeyvalue,charindex('~', @oldGroupKeyvalue, 1)+ 1)+1)

    --PRINT @FirstPos
    --PRINT @LastPos

    --SELECT SUBSTRING(@oldGroupKeyvalue,@FirstPos,(@LastPos-@FirstPos))
    SELECT @oldGroupKeyvalue
    SELECT STUFF(@oldGroupKeyvalue,@FirstPos,(@LastPos-@FirstPos),@newXfundCode)