title: Trouble replacing abbreviated text correctly in SQL
body: <p>I am trying to replace a bunch of difficult to decipher abbreviations with corresponding detailed descriptions. I have a table called Abbreviations that simply holds a list of abbreviations to look for and the corresponding descriptions they should be changed to. Additionally the "Replaced" table holds a list of unaltered abbreviated descriptions that I would like to change in a single column called "DescriptionCodes"</p>
  
  <p>The data I am trying to change is a list of different teas. For instance the entry
  "TADIN H-B GR" would be the abbreviation for "TADIN HERBAL BAG GREEN"</p>
  
  <p>The SQL Code I am currently using looks like this:</p>
  
  <pre><code>BEGIN TRANSACTION
  DECLARE @Desc varchar(500)
  DECLARE @Abbr varchar(500)
  
  DECLARE contact_cursor CURSOR FOR
  SELECT Description, Abbrv FROM dbo.Abbreviations
  
  OPEN contact_cursor
  
  FETCH NEXT FROM contact_cursor
  INTO @Desc, @Abbr
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    PRINT 'Changing ' + @Abbr + ' to ' + @Desc
  
    UPDATE Replaced
    SET DescriptionCodes = REPLACE(DescriptionCodes, @Abbr, @Desc)
    WHERE DescriptionCodes LIKE CONCAT('% ', @Abbr, ' %')
  
    FETCH NEXT FROM contact_cursor
    INTO @Desc, @Abbr
  END
  
  CLOSE contact_cursor
  DEALLOCATE contact_cursor
  
  COMMIT
  </code></pre>
  
  <p>Of course the problem I am running into is that once an abbreviation is applied the detailed description may contain a substring that matches another abbreviation that is then applied. For instance PKG might be changed to PACKAGE but PA is the abbreviation for PINEAPPLE as well, meaning that once that change is applied the final result would be PINEAPPLECKAGE. Every abbreviation has a single blank space on either side so I thought to use that fact to not update any abbreviation without a blank space on either side, hence the
  CONCAT('% ', @Abbr, ' %') in my code. However, when I try this method basically nothing gets changed at all. I've been able to see limited success by removing that constraint but the other issue then happens. Any ideas as to how I could make sure that only occurrences of abbreviations with a blank space on either side are considered and updated?</p>
  
  <p>UPDATE:
  After trying several of the solutions posted here, I still haven't been able to get it to work and I'm not sure why. By all appearances this is correct. Here is a sample of some of the data I'm working with:</p>
  
  <pre><code>JUSTEA HBL PKG CMCL CHM LG LR  1.5OZ
  PRIDE OF INDIA BG ASM B BKFST  25 CT
  CTL BR H-B 7BLSM PP            1 CT
  POSTI H-B HRH CRN              20 CT
  DRS H-B EPGP LPLDS PTVP TGN    20 CT
  ULTLC BG CHG                   100 CT
  PG TIPS BG D B                 40 CT
  RPBL R-B B HLDY FT BLD         6 CT
  </code></pre>
  
  <p>This is fine and looks like it should convert with no issues. Yet when I run the code with spaces indicated (As with "LIKE '% ' + @Abbr + ' %'", which was my first inclination) the data remains completely unchanged. If I remove them the data becomes completely unintelligible. For example the line beginning with PRIDE OF INDIA becomes</p>
  
  <p>PRIDE OF INDIA(N) IRISH AFTERNOON  BLACK AG  ASIA PLUM RICOT SPICE(D) EARMINT  BLACK  BLACK KFST  25 CURRANT AN AID N T</p>
  
  <p>I feel I should note that this data was imported from Excel Spreadsheets originally. Is there any chance that has anything to do with the spaces not being recognized?</p>