Replacing Multiple Values in a String

javascript replace multiple strings with one string
javascript replace multiple matches
string replace multiple python
string.replace multiple c#
string replace multiple java
replacing multiple characters in a string js
multiple arguments in replace
golang replace multiple characters in string

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

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.

How can I replace multiple characters in a string?, However, a simple replace only replaces the first occurence. timeEnd('double replace big string'); console.time('single replace big string'); var in obj){ const value = obj[o]; if (each == o){ each = value; } } finalString += each  Replace multiple characters / strings from a string # Replace all the occurrences of string in list by AA in the main list otherStr = replaceMultiple ( mainStr , [ 's' , 'l' , 'a' ] , "AA" )

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>

JavaScript, JavaScript | Replace multiple strings with multiple other strings. replace() method. This method searches a string for a defined value, or a regular expression, and returns a new string with the replaced defined value. Syntax: JavaScript Array join() Method. This method adds the elements of an array into a string, and replace multiple values at the same time - in order to convert a string to a number. I am trying to convert a varchar field to a number, however, there is a set of common characters inside that field that need to be removed in order for me to successfully convert it to numeric.

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!

JavaScript String Replace Magic / Coder's Block, Replacing strings in JavaScript is a fairly common task, but there are some nuances replace() does not change the value of str , rather it returns a new string with the applied For example, replacing multiple words at once. In this post we’re going to explore how to find and replace multiple text strings from within another string. Download Example File Excel has a great built in function called SUBSTITUTE which allows you to find one bit of text within another text string and substitute it for another bit of text. Copy …

Python program to replace single or multiple character,substring in a , How do you replace multiple items in a string in python? If you want to replace multiple characters you can call the String.prototype.replace() with the replacement argument being a function that gets called for each match. All you need is an object representing the character mapping which you will use in that function.

How to Replace Character Inside a String in JavaScript, Hello World : e with E and o with O. I want to replace multiple instances of strings within this file, and I tried using PowerShell to do the job. It works fine for a single replace, but doing multiple replaces is very slow because each time it has to parse the whole file again, and this file is very large.

Excel formula: Find and replace multiple values, How do you replace a single character in a string JavaScript? String strOutput = inString.replace("call me","cm").replace("as soon as possible","asap"); Alternatively, if you have many such replacements, it might be wiser to store them in some kind of data structure such as a 2d-array.

Comments
  • 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.
  • I borrowed your table variable, thx for this and an upvote from my side.
  • 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, and you really don't want to do that.
  • @zohar Iets see your set-based solution cursor-free, loop-free, rCTE-free way of solving this. I'll be waiting :)
  • @AlanBurstein use a string splitting function like Jeff Moden's DelimitedSplit8K (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?
  • 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.
  • Upvoted for not being as lazy as I was and posting the string splitter solution :-)
  • This is currently the best answer here. I wonder why it was downvoted.
  • @ZoharPeled Sometimes people just don't understand a good answer :-) Thx for your support!
  • 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)
  • Yes, I didn't think about that. Anyway the correct solution is still to normalize the database.
  • 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.
  • Gotta love them vendor databases!