Replace Matching Row String From Another Column

Question

I have this table:

ID NewName  OldName  Link
 1 NewName1 OldName1 OldName2|OldName3
 2 NewName2 OldName2 OldName1|OldName3
 3 NewName3 OldName3 OldName1|OldName2

What I want to happen is to change all the OldName on the Link column to the NewName. Like this:

ID NewName  OldName  Link
 1 NewName1 OldName1 NewName2|NewName3
 2 NewName2 OldName2 NewName1|NewName3
 3 NewName3 OldName3 NewName1|NewName2

Can anyone suggest what's the best way to do this?

The best way to do this is to not store pipe delimited data in your table. This will require a really ugly query. — Tim Biegeleisen, Oct 27 '17 at 08:32
Unfortunately, I don't "own" the table. I am just converting an old table into the new one. — Kevin, Oct 27 '17 at 08:37
@Yogesh Thanks for your answer. I am currently trying to make the proposed solutions to work on my scenario. I was unable to point out that the `Link` column can have more than 2 values — Kevin, Oct 30 '17 at 05:34

Yogesh Sharma · Accepted Answer · 2017-10-27T10:17:34.317

You are looking to Change the value of Link according OldNames to its with New Names:

First you will need to split your Link data delimited by |into row & then Join with Your Table

SELECT TTT.ID,
       TTT.[NewName],
       TTT.OldName,
       [Link] = STUFF(
                     (
                         SELECT 
                                '|'+[Link]
                         FROM
                         (
                             SELECT AA.ID,
                                    AA.[NewName],
                                    AA.OldName,
                                    T.[NewName] [Link]
                             FROM
                             (
                                 SELECT ID,
                                        NewName,
                                        OldName,
                                        split.x.value('.', 'NVARCHAR(MAX)') DATA
                                 FROM
                                 (
                                     SELECT ID,
                                            NewName,
                                            OldName,
                                            CAST('<M>'+REPLACE(Link, '|', '</M><M>')+'</M>' AS XML) AS String
                                     FROM <table_name>
                                 ) AS a
                                 CROSS APPLY String.nodes('/M') AS split(x)
                             ) AA
                             INNER JOIN <table_name> T ON T.OldName = AA.DATA
                         ) TT
                         WHERE TT.ID = TTT.ID FOR XML PATH('')
                     ), 1, 1, '')
FROM <table_name> TTT;

Result :

ID  NewName     OldName     Link
1   NewName1    OldName1    NewName2|NewName3
2   NewName2    OldName2    NewName1|NewName3
3   NewName3    OldName3    NewName1|NewName2

Hi Yogesh, if your answer supports arbitrary numbers of piped data, then it is more robust than mine. But really neither of us should be recommending storing data in this way. — Tim Biegeleisen, Oct 27 '17 at 09:53
@TimBiegeleisen you're right thats why i vote ur first comment. — Yogesh Sharma, Oct 27 '17 at 10:13

Vahid · Answer 2 · 2017-10-27T11:03:56.290

0

MSSQL: To store delimited-values from other column/other sources to a specific row, you may use FOR XML PATH, look at this SO thread:

UPDATE YourTable
SET Link = SUBSTRING(
        (
            SELECT '|' + T2.NewName
            FROM YourTable T2
            WHERE '|'+T2.Link+'|' LIKE '%|'+YourTable.OldName+'|%'
            FOR XML PATH ('')
        ), 2, 1000);

MYSQL: If you want to store delimited-values from other column/other sources to a specific row, you may use mysql GROUP_CONCAT function:

UPDATE table t1
SET Link = (
    SELECT GROUP_CONCAT(t2.NewName SEPARATOR '|')
    FROM table t2 WHERE FIND_IN_SET(t2.OldName, REPLACE(t1.Link, '|', ','))
)

I assumed that you want to replace any old values in the Link column with its new value. See the results in action on dbfiddle.uk

edited Oct 27 '17 at 11:03

answered Oct 27 '17 at 08:37

Vahid

940
4
14

1

Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Oct 27 '17 at 09:07
1

The OP claims it is using SQL Server, not MySQL. And in any case `GROUP_CONCAT` seems irrelevant here. – Tim Biegeleisen Oct 27 '17 at 10:14
@TimBiegeleisen thanks for your comment. I updated the answer. – Vahid Oct 27 '17 at 11:06

score 0 · Answer 3 · edited May 20 '19 at 14:44

If the Link column have only two names always, then we try self joining twice to match the respective new names which should be used for replacement. The join condition is ugly, but this is the price paid for storing denormalized data in your table.

WITH cte AS 
(
    SELECT t1.Link, t2.NewName AS NewNameLeft, t3.NewName AS NewNameRight
    FROM yourTable t1
    LEFT JOIN yourTable t2
        ON SUBSTRING(t1.Link, 1, CHARINDEX('|', t1.Link) - 1) = t2.OldName
    LEFT JOIN yourTable t3
        ON SUBSTRING(t1.Link,
                     CHARINDEX('|', t1.Link) + 1,
                     LEN(t1.Link) - CHARINDEX('|', t1.Link)) = t3.OldName
)
UPDATE cte
SET Link = NewNameLeft + '|' + NewNameRight
WHERE NewNameLeft IS NOT NULL AND NewNameRight IS NOT NULL;

Note that this answer assumes that each old name appears only once in the table. I default to not doing an update unless both left and right new names are found.

score -1 · Answer 4 · answered Oct 27 '17 at 08:37

-1

I guess you just need REPLACE

select id, NewName, OldName, replace(link, 'OldName', 'NewName') Link
from your_data

and if you need to do it directly in table then use

update your_data
set link = replace(link, 'OldName', 'NewName')

answered Oct 27 '17 at 08:37

Radim Bača

10,646
1
19
33

Thank you for your answer @Radim. I don't know who downvoted this but I tried your answer and it doesn't work for me. – Kevin Oct 30 '17 at 01:00

Replace Matching Row String From Another Column

4 Answers4