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

Question

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’

Answer 1

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.