How to find a name=id pair in an sql string and replace it with another value and update it?-CodePudding

I have a lot of records in a table News.

Each record has NewsRoleId, NewsTitle and a URL.

The URL is like

http://ournews.com/View-News;NewsId=56122;OrderId=1;pt=5

Each NewsTitle would return you exactly 2 records; not more not less but different NewsRoleID; for example:

    ID         NewsTitle       NewsRoleId       URL
    1             Test            124           http://ournews.com/View;newsId=44;OrderId=1;pt=5
   `2             Test            138`          http://ournews.com/View;newsId=32;OrderId=1;pt=5

Now, the goal is to UPDATE the newsId in URL by ID of one record with another i.e. Id of the record with NewsRoleID= 124 should be updated in the newsId of URL of record with NewsRoleId= 138 and vice versa.

Desired Output:

D         NewsTitle       NewsRoleId       URL
1           Test              124          http://ournews.com/View;newsId=2;OrderId=1;pt=5

2           Test              138          http://ournews.com/View;newsId=1;OrderId=1;pt=5

I have written an update query.

Update News
SET URL= REPLACE(Url, 'newsID=123433', 'newsId=' CAST(Select Id from News where NewsTitle= 'test' and NewsRoleID= 124) as varchar)
where NewsRoleID = 138 and NewsTitle = 'test'

But Problem with this is that I cannot exactly find the pair ‘NewsId=the Id which is random’

CodePudding user response：

Does this work for you?:

declare @news table 
    (
        ID int,
        NewsTitle nvarchar(100),
        NewsRoleId int,
        [URL] nvarchar(255)
    );

INSERT INTO @news VALUES
(1, 'Test', 124, 'http://ournews.com/View;newsId=44;OrderId=1;pt=5'),
(2, 'Test', 138, 'http://ournews.com/View;newsId=32;OrderId=1;pt=5');

WITH CTE AS
(SELECT ID, CHARINDEX('newsId=', [URL], 1) AS nPos FROM @news),
CTE2 AS
(SELECT n.ID, CHARINDEX(';', [URL], c.nPos) AS scPos FROM @news 
n INNER JOIN CTE c ON n.ID = c.ID )
UPDATE n
SET URL = SUBSTRING(n.[URL], 1, c1.NPos -1) 
  'newsId='   cast(nOther.Id as nvarchar)
  SUBSTRING(n.[URL], c2.scPos, 300)
FROM CTE c1 
INNER JOIN CTE2 c2 ON c1.ID = c2.ID
INNER JOIN @news n ON c1.ID = n.ID
INNER JOIN @news nOther ON n.NewsTitle = nOther.NewsTitle 
    AND n.NewsRoleId <> nOther.NewsRoleId;
    
SELECT * FROM @news;

Output:

ID	NewsTitle	NewsRoleId	URL
1	Test	124	http://ournews.com/View;newsId=2;OrderId=1;pt=5
2	Test	138	http://ournews.com/View;newsId=1;OrderId=1;pt=5

By way of explanation, I use two CTEs to identify the position where "newsid=" occurs and the first semi-colon following this. The trick is then to build the new string using a self-join on the news table, with the same NewsTitle but differing NewsRoleIDs. This allows us to put the "Other" ID into the new URL.

CodePudding user response：

You may try the following:

With CTE AS
(
  Select ID, NewsTitle, NewsRoleId, URL,
       REPLACE(URL, 
               SUBSTRING(URL, 
                         CHARINDEX(';', URL) 1,  
                         CHARINDEX(';', URL, CHARINDEX(';', URL) 1) -(CHARINDEX(';',URL))
                        ),
               CONCAT('newsId=',Case When ROW_NUMBER() Over (Partition By NewsTitle Order By ID) = 1
                    Then LEAD(ID) Over (Partition By NewsTitle Order By ID)
                    ELSE LAG(ID) Over (Partition By NewsTitle Order By ID)
               End,';')
              ) NEW_URL
From News
)

Update CTE Set URL = NEW_URL;

Select * From News;

See a demo from db<>fiddle.