How to find & replace ~ separated value

Question

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

Your answer

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

Answer 1

Yitzhak Khabinsky 27,196

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

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

Answer 2

Viorel 127.3K

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

Sudip Bhatt 2,281 Reputation points

2020-12-03T16:33:36.747+00:00

Sir please help me to understand your code. if possible break it up into multiple section and tell me what each part is doing.

Thanks
Viorel 127.3K Reputation points

2020-12-03T17:56:16.163+00:00
This part:

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)

creates four things:

t.s — the original string
t1.i — the index of the first ‘~’
t2.i — the index of the second ‘~’
t3.i — the index of the third ‘~’.

Then select uses these values to process the strings. Substring extracts “TRIN”, i.e. the value between the second and third ‘~’. Left and Right extract parts that are concatenated with “RD_101” to make the new ‘~’-separated string.

You can re-write this query using separate variables and a series of statements.
Sudip Bhatt 2,281 Reputation points

2020-12-05T14:48:27.26+00:00

sir please tell me why did you cross apply here ?

in what kind of scenario people use cross apply? please discuss cross apply usage with example.

thanks
Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator

2020-12-05T16:03:10.09+00:00

You have asked about CROSS APPLY before, and I have answered it before.

And in this case - Viorel gives you an example of how to use CROSS APPLY, and he also explains it. Now spend time on understanding the explanation. Pick the statement apart piece by piece to see what it returns.

Answer 3

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)

Answer 4

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

Sudip Bhatt 2,281 Reputation points

2020-12-04T07:34:56.733+00:00

Thank you Sir for your post.

string_agg is new function ? what it does ?

What is WITHIN GROUP ?
Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator

2020-12-04T22:26:30.17+00:00

string_agg was added in SQL 2017. It produces an concatenated list of the values from all rows, with the separator you specify. The same thing you used FOR XML PATH('') in earlier version, but a lot more intuitive. WITHIN GROUP permits you to specify the order of the values.

Here is an example query:

SELECT o.name, string_agg(c.name, ' - ') WITHIN GROUP (ORDER BY c.column_id) FROM sys.objects o JOIN sys.columns c ON c.object_id = o.object_id GROUP BY o.name

Note the GROUP BY. That is, this is an aggregate function like SUM, AVG etc.
Sudip Bhatt 2,281 Reputation points

2020-12-05T15:52:23.35+00:00

What is COLLATE Slovenian_BIN2 ?
Erland Sommarskog 138.6K Reputation points MVP Volunteer Moderator

2020-12-05T18:28:23.087+00:00

It is a small performance enhancement. I force a binary collation, which is a lot faster than a normal Unicode-based search where the separator will be matched against equivalent characters.

Answer 5

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

Sudip Bhatt 2,281 Reputation points

2020-12-04T07:35:43.01+00:00

Thank you Madam for your answer.
MelissaMa-msft 24,246 Reputation points Moderator

2020-12-04T07:57:26.023+00:00

Hi @Sudip Bhatt ,

Adding some notes as below:

dbo.GetSplitString(@oldGroupKeyValue, '~',3) ---3 refers from 2nd ~ to 3rd ~

If you would like to pick value from 3rd ~ to 4th ~ , you could use dbo.GetSplitString(@oldGroupKeyValue, '~',4) and so on.

String_agg is a new function which applies to SQL Server 2017 and later. If your version of SQL Server is 2016 and earlier, you could consider the user defined function.

Best regards
Melissa