Looping in same row for same column in sql server

Question

Hi all,

I am trying to do loop to update two columns based on a particular string which is repeated in a row in particular column i.e.,
col1 - asd abc 34445 qwert 34 abc 45543 test cold
so here I am trying to remove all strings with "abc " and next following number and concatenating into other column but the condition is not satisfied as only once the value is removed n concatenated to other column but next value is not getting removed
i.e.,
Actual result should be
COL1 - asd qwert 34 test cold
COL2 - abc 34445 abc 45543
but am getting
COL1 - asd qwert 34 abc 45543 test cold
COL2 - abc 34445

so is there any other method to achieve this ?53282-temp-table-replace.pdf

Answer 1

It is an interesting task. XML and XQuery to the rescue. XML is based on ordered sequences. Exactly what we need.

It is a two step solution:

1. Converting column into XML data type and, additionally, constructing an XML fragment with the positions of tokens to exclude.
2. Reversing back to strings. First, excluding not needed tokens based on their position, Second, generating new string containing tokens that were excluded.

SQL

-- DDL and sample data population, start
DECLARE @tbl table([key] INT PRIMARY KEY, [CMA ID] varchar(max), [PA ID] varchar(max), [tbm] varchar(max),[PEND] varchar(max));
INSERT INTO @tbl ([key],[CMA ID],[PA ID],tbm,PEND) VALUES 
(1,'TEMP: 58498','175 TEST1 local TEMP 58521 117 TEST1 HIX TEMP 58524 207 TEST1 SAM','175 TEST1 local TEMP 58521 117 TEST1 HIX TEMP 58524 207 TEST1 SAM',''),
(3,'TEMP: 58500','153 TEST1 Local TEMP 58539 3 TEST1 HOST TEMP 58540 582 TEST1 SAM TEMP 58541 357 TEST1 HIX', '153 TEST1 Local TEMP 58539 3 TEST1 HOST TEMP 58540 582 TEST1 SAM TEMP 58541 357 TEST1 HIX',''),
(4,'TEST:','4 TEST1 55066 11 TEST1 55136 TEMP 55137 31 TEST1','4 TEST1 55066 11 TEST1 55136 TEMP 55137 31 TEST1',''),
(2,'TEMP: 59131','174 TEST1 includes TEMP 59233 and 59234','174 TEST1 includes TEMP 59233 and 59234',''),
(6,'TEMP: 58582','115 TEST1 local TEMP 58585 2309 TEST1 HOST TEMP 58594 115 TEST1 SAM','115 TEST1 local TEMP 58585 2309 TEST1 HOST TEMP 58594 115 TEST1 SAM','');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1)
 , @searchString VARCHAR(20) = 'TEMP';

;WITH rs AS
(
 SELECT [key], [PA ID]
   , TRY_CAST(N'<root><r>' + 
 REPLACE(CAST([PA ID] AS NVARCHAR(MAX)), @separator, '</r><r>') + '</r>' +
    TRY_CAST(TRY_CAST('<root><r>' + 
 REPLACE(CAST([PA ID] AS NVARCHAR(MAX)), @separator, '</r><r>') + '</r></root>' AS XML
 )
 .query('<seq>
 {
    for $x in /root/r/text()
    let $pos := count(/root/r[. << $x[1]])
    return if ($x eq sql:variable("@searchString")) then (<r>{$pos}</r>,<r>{$pos + 1}</r>)
    else ()
 }
 </seq>') AS NVARCHAR(MAX))
    + '</root>' AS XML) AS xmldata
 FROM @tbl
)
SELECT *
 , xmldata.query('
 for $x in /root/r[not(position()=(/root/seq/r))]/text()
 return data($x)
 ').value('.','NVARCHAR(MAX)') AS [excluded]
 , xmldata.query('
 for $x in /root/r[position()=(/root/seq/r)]/text()
 return data($x)
 ').value('.','NVARCHAR(MAX)') AS [what_excluded]
FROM rs;

For example, for the key=2, the xmldata column contains XML of the following structure:

<root>
 <r>174</r>
 <r>TEST1</r>
 <r>includes</r>
 <r>TEMP</r>
 <r>59233</r>
 <r>and</r>
 <r>59234</r>
 <seq>
 <r>4</r>
 <r>5</r>
 </seq>
</root>

Answer 2

Hey,
you can use a while loop and the counter would be the initial count of the number of occurrences of characters in the string.
And in each iteration replace the original occurrence and its next numbers with blank and update it in 2nd column

Answer 3

Experts have provided some suggestions. Has your problem been solved?

Echo

Answer 4

@Charan P

This is a string parsing based method to get same result.

Create a parsing UDF like this

parsing-a-delimited-string

Then use it like below

DECLARE @tbl table([key] INT PRIMARY KEY, [CMA ID] varchar(max), [PA ID] varchar(max), [tbm] varchar(max),[PEND] varchar(max));  
 INSERT INTO @tbl ([key],[CMA ID],[PA ID],tbm,PEND) VALUES   
 (1,'TEMP: 58498','175 TEST1 local TEMP 58521 117 TEST1 HIX TEMP 58524 207 TEST1 SAM','175 TEST1 local TEMP 58521 117 TEST1 HIX TEMP 58524 207 TEST1 SAM',''),  
 (3,'TEMP: 58500','153 TEST1 Local TEMP 58539 3 TEST1 HOST TEMP 58540 582 TEST1 SAM TEMP 58541 357 TEST1 HIX', '153 TEST1 Local TEMP 58539 3 TEST1 HOST TEMP 58540 582 TEST1 SAM TEMP 58541 357 TEST1 HIX',''),  
 (4,'TEST:','4 TEST1 55066 11 TEST1 55136 TEMP 55137 31 TEST1','4 TEST1 55066 11 TEST1 55136 TEMP 55137 31 TEST1',''),  
 (2,'TEMP: 59131','174 TEST1 includes TEMP 59233 and 59234','174 TEST1 includes TEMP 59233 and 59234',''),  
 (6,'TEMP: 58582','115 TEST1 local TEMP 58585 2309 TEST1 HOST TEMP 58594 115 TEST1 SAM','115 TEST1 local TEMP 58585 2309 TEST1 HOST TEMP 58594 115 TEST1 SAM','');  
 ;With Temp  
 AS  
 (  
select [Key],ID,  
CASE WHEN PATINDEX('%TEMP.[0-9]%',Val) > 0 THEN STUFF(Val,1,CHARINDEX(' ',Val),'') ELSE Val END AS Excluded,  
REPLACE(CASE WHEN PATINDEX('%TEMP.[0-9]%',Val) > 0 THEN  LEFT(Val,CHARINDEX(' ',Val) -1 ) ELSE '' END,'.',' ') AS Needed  
FROM (select *,replace([PA ID],'TEMP ','$$$TEMP.') AS s FROM @tbl)t  
CROSS APPLY dbo.ParseValues(t.s,'$$$')f  
)  
  
SELECT [Key],  
STRING_AGG(Excluded,' ') WITHIN GROUP (ORDER BY ID) AS Excluded,  
STRING_AGG(Needed,' ') WITHIN GROUP (ORDER BY ID) AS Needed  
FROM Temp t  
GROUP BY [key]