SQL Replace() table join only replaces trailing te

2019-08-31 02:01发布

问题:

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. :)

回答1:

OK, this is pretty stupid/funny. A friend asked if I was using TRIM()

SELECT InitialString, ReplacementTarget, ReplacementValue, 
       REPLACE(InitialString, RTRIM(ReplacementTarget), RTRIM(ReplacementValue)) 
            AS ReplacedString
FROM   StringTable CROSS JOIN ReplacementTable

Makes perfect sense, in hindsight. Only the TRAILING target had the requisite trailing spaces!