Replacing Multiple Values in a String

Question

I am combining two databases of financial transactions. In one of the tables there is a field used to represents the allocation of the transaction with another record in the same table. It uses the Record ID and Amount to reference the corresponding record. When combining the data from the two table of the two databases, the Record ID will change. How do I change all the Record IDs within this field, from the old ID to the new corresponding ID? Is there a way. The RecordID is the "I=" values that needs to be replaced with the NewRecordID.

In other words how do I for example, replace the value I=53983 with I=3 everywhere in the Allocation field and the same with all the other "I=" values?

NewID    OldID    Allocation
1        53033    I=53983;A=260.28
2        53038    I=53983;A=2922.59|I=54420;A=2194.19
3        53983    I=53033;A=260.28|I=54261;A=62.93|I=53038;A=2922.59
4        54261    I=53983;A=62.93
5        54420    I=53038;A=2194.19

I corrected it, sorry about that. I originally copied it from notepad but the tabs and spacing didn't work so well. I added dots between the columns in order to get the column names and values inline. — Jacques, Jul 11 '18 at 11:54

score 1 · Answer 1 · answered Jul 11 '18 at 18:12

1

Normally I would not suggest using a loop like this but I can't think of a cleaner way.

-- Your table
DECLARE @table TABLE ([newid] INT, oldId INT, Allocation VARCHAR(8000));
INSERT @table
([newid],oldId,Allocation)
VALUES 
(1,53033,'I=53983;A=260.28'),
(2,53038,'I=53983;A=2922.59|I=54420;A=2194.19'),
(3,53983,'I=53033;A=260.28|I=54261;A=62.93|I=53038;A=2922.59'),
(4,54261,'I=53983;A=62.93'),
(5,54420,'I=53038;A=2194.19');

-- table to collect all the ids
DECLARE @ids TABLE (id INT);
INSERT @ids(id)
SELECT [newid] 
FROM @table;

-- routine to perform the update
DECLARE @id INT = 1;
WHILE @id <= (SELECT COUNT(*) FROM @table)
BEGIN
  UPDATE @table 
         SET Allocation = REPLACE(t.Allocation,f.old,'I='+CAST(@id AS VARCHAR(10))+';')
  FROM @table t
  CROSS APPLY (VALUES 
  ('I='+CAST(
    (SELECT t.oldId
     FROM @table t
     WHERE t.newid = @id)
    AS VARCHAR(10))+';')) f(old)
  WHERE CHARINDEX(f.old, t.Allocation) > 0;

  SET @id += 1;
END

SELECT * FROM @table;

Returns

newid       oldId       Allocation
----------- ----------- ------------------------------------------
1           53033       I=3;A=260.28
2           53038       I=3;A=2922.59|I=5;A=2194.19
3           53983       I=1;A=260.28|I=4;A=62.93|I=2;A=2922.59
4           54261       I=3;A=62.93
5           54420       I=2;A=2194.19

Note that my solution assumes that the newId value is a sequential set of numbers beginning with 1. You could easily tweak this if that's not the case.

answered Jul 11 '18 at 18:12

Alan Burstein

7,770
1
15
18

2

I borrowed your table variable, thx for this and an upvote from my side. – Shnugo Jul 11 '18 at 22:23
You should avoid using loops in SQL as much as possible. SQL works best on a set based approach (manipulation sets or rows) . Using loops means going [RBAR](https://www.red-gate.com/simple-talk/sql/t-sql-programming/rbar-row-by-agonizing-row/), and you really don't want to do that. – Zohar Peled Jul 12 '18 at 05:28
@zohar Iets see your set-based solution cursor-free, loop-free, rCTE-free way of solving this. I'll be waiting :) – Alan Burstein Jul 12 '18 at 06:25
@AlanBurstein use a string splitting function like Jeff Moden's [DelimitedSplit8K](http://www.sqlservercentral.com/articles/Tally+Table/72993/) (you don't want the built in string_split since you need to keep the order of the items as they where in the string) and you just use a `cross apply` to get the newId, oldId, and all Allocation values. Insert that result to a temporary table, do a self join update, and then re-generate the strings and update the original table. Shnugo's answer shows a way to do that with an XML based string splitter, Do we really need another answer like that? – Zohar Peled Jul 12 '18 at 06:53
Well done @ZoharPeled your solution is excellent. I was short on time and could not come up with a better, set-based solution than what I posted (note that I posted my solution 4 hours before anyone else). I would add that, what you posted is notably faster when run with a parallel execution plan but does not get one (on my system anyhow) unless you force a parallel plan using traceflag 8649 or `make_parallel()` by Adam Machanic. – Alan Burstein Jul 12 '18 at 18:27
Well the excellent part here is Jeff's function, the rest is quite simple... Whenever you see delimited strings in a database, usually the first thing to do is split them into rows. It makes everything much simpler. – Zohar Peled Jul 12 '18 at 20:22

score 1 · Answer 2 · answered Jul 11 '18 at 22:23

As you were told already, the table's design is bad. With a proper design you would not have to solve this problem.

The following approach will use some replacements in order to get the concatenated string (an awful idea!) as structured XML. The result of the CTE RowWise should be enough for you in order to repair the bad design.

If you really need to stay with this, the final SELECT will return all your data re-organized:

DECLARE @table TABLE ([newid] INT, oldId INT, Allocation VARCHAR(8000));
INSERT @table
([newid],oldId,Allocation)
VALUES 
(1,53033,'I=53983;A=260.28'),
(2,53038,'I=53983;A=2922.59|I=54420;A=2194.19'),
(3,53983,'I=53033;A=260.28|I=54261;A=62.93|I=53038;A=2922.59'),
(4,54261,'I=53983;A=62.93'),
(5,54420,'I=53038;A=2194.19');


WITH Splitted AS
(
    SELECT * 
          ,CAST('<x><y type="' + REPLACE(REPLACE(REPLACE(Allocation,'=','">'),';','</y><y type="'),'|','</y></x><x><y type="') + '</y></x>' AS XML) AS TheXml
    FROm @table
)
,RowWise AS
(
    SELECT Splitted.oldId
          ,x.value('(y[@type="I"]/text())[1]','int') AS I
          ,x.value('(y[@type="A"]/text())[1]','decimal(10,4)') AS A
    FROM Splitted
    CROSS APPLY TheXml.nodes('/x') A(x)
)
SELECT t1.[newid],t1.oldId
      ,STUFF(
      (
        SELECT CONCAT('|','I=',t3.[newid],';A=',t2.A)
        FROM RowWise t2
        INNER JOIN @table t3 ON t2.I=t3.[oldId]
        WHERE t2.oldId =t1.oldId
        FOR XML PATH('')
       ),1,1,'')
FROM @table AS t1;

The replacements and the cast to XML will return something like this:

<x>
  <y type="I">53983</y>
  <y type="A">2922.59</y>
</x>
<x>
  <y type="I">54420</y>
  <y type="A">2194.19</y>
</x>

Upvoted for not being as lazy as I was and posting the string splitter solution :-) — Zohar Peled, Jul 12 '18 at 04:41
This is currently the best answer here. I wonder why it was downvoted. — Zohar Peled, Jul 12 '18 at 07:01
@ZoharPeled Sometimes people just don't understand a good answer :-) Thx for your support! — Shnugo, Jul 12 '18 at 09:21

score 0 · Answer 3 · edited Jul 11 '18 at 18:15

0

Assuming that's the data in your table, you could do something as simple as this:

UPDATE MyTable
SET Allocation = REPLACE(Allocation, 
                         'I='+ CAST(OldID as varchar(11)) + ';', 
                         'I='+ CAST(NewID as varchar(11)) + ';')

However, please note it's a very bad practice to keep more then one data entity in a single column.
If possible, you better normalize your database - create a new table for Allocations, that contains columns for all the data parts currently in the column, and have a foreign key from it referencing the existing table.

For further reading: read the SO post Is storing a delimited list in a database column really that bad? and it's answers, where you will see a lot of reasons why the answer to this question is Absolutely yes!

edited Jul 11 '18 at 18:15

Alan Burstein

7,770
1
15
18

answered Jul 11 '18 at 12:00

Zohar Peled

79,642
10
69
121

This would not work because the newId needs to be applied to ALL of the rows that contain the oldId (not just the current row) – Alan Burstein Jul 11 '18 at 18:14
Yes, I didn't think about that. Anyway the correct solution is still to normalize the database. – Zohar Peled Jul 11 '18 at 19:21
1

Thank you Zohar and Alan for the replies. I fully understand that it is a bad practice as you mentioned above. The database is one on Sage's Financial Software products. That's the way they wrote it and that how the front end product uses the data. I am merely trying to consolidate the data from two databases into one. But thank you very much. – Jacques Jul 12 '18 at 07:20
Gotta love them vendor databases! – Alan Burstein Jul 12 '18 at 15:06

Replacing Multiple Values in a String

3 Answers3