I'm using .Net 2013, which I believe comes packaged with a version of SQL Server.
EDIT: I understand the example is rather odd, it's just an illustration of an issue I encountered while working out a resolution to THIS QUESTION
EDIT 2:
My expected results are apparently NOT what people are expecting, so adding to bottom. Sorry for the confusion.
Hopefully Final Edit I changed the tables from variations of Hello World to Bacon & Eggs. After reading this a few times to make sure I'm very clear on what the issue is, I was going cross-eyed. Changed the source data to be more legible. Issue remains. I checked.
I have two tables. One containing a string. One containing a string to be replaced, as well as its replacement.
*String Table*
Bacon
Eggs
Bacon & Eggs
Eggs & Bacon
*Replacement Table*
Bacon Pork
Eggs Poultry
I then have a View that does a cross join to perform the replace, but it is not working properly. This has me baffled, because it seems to only replace the string at the END. See the query results below.
SELECT InitialString, ReplacementTarget, ReplacementValue,
REPLACE(InitialString, ReplacementTarget, ReplacementValue)
AS ReplacedString
FROM StringTable CROSS JOIN ReplacementTable
Note that only the last half of the text is replaced. It's not like the first replacement is made, then it stops. That at least would make sense. This is literally like the replacement isn't validating for some reason.
RESULTS OF QUERY (issues marked with asterisk)
InitialString ReplaceTarget ReplaceValue ReplacedString
Bacon Bacon Pork Pork
Eggs Bacon Pork Eggs
Bacon & Eggs Bacon Pork Bacon* & Eggs
Eggs & Bacon Bacon Pork Eggs & Pork
Bacon Eggs Poultry Bacon
Eggs Eggs Poultry Poultry
Bacon & Eggs Eggs Poultry Bacon & Poultry
Eggs & Bacon Eggs Poultry Eggs* & Bacon
. EXPECTED RESULTS (YES, I WANT 8 ROWS BACK. SEE LINK ABOVE) (changes denoted with asterisk)
InitialString ReplaceTarget ReplaceValue ReplacedString
Bacon Bacon Pork Pork
Eggs Bacon Pork Eggs
Bacon & Eggs Bacon Pork Pork* & Eggs
Eggs & Bacon Bacon Pork Eggs & Pork
Bacon Eggs Poultry Bacon
Eggs Eggs Poultry Poultry
Bacon & Eggs Eggs Poultry Bacon & Poultry
Eggs & Bacon Eggs Poultry Poultry* & Bacon
.
Any help or insight into this issue would be greatly appreciated. :)