title: Using T-SQL remove specified words from a string
body: <p>I am trying to eliminate certain strings from a Description field in a table. To this end I have made this function</p>
  
  <hr>
  
  <pre><code>CREATE FUNCTION fnDescriptionClean 
  (@strDescription varchar(50))
  RETURNS varchar(50)
  AS
  BEGIN
  
  declare @Return varchar(50)
  declare @badword varchar(50)
  
  set @badword = 'Front'
  set @strDescription = CASE 
  
  --Remove from mid string
  
  WHEN @strDescription LIKE '% ' + @Badword +' %'  THEN REPLACE(@strDescription,' ' +  @Badword + ' ',' ')
  
  --Remove from start of string
  
  WHEN @strDescription LIKE @Badword +' %' THEN RIGHT(@strDescription, (len(@strDescription)-(len(@Badword)+1)))
  
  --Remove from end of string
  
  WHEN @strDescription LIKE '% ' + @Badword THEN LEFT(@strDescription, (len(@strDescription)-(len(@Badword)+1)))
  ELSE @strDescription END 
  
  set @badword = 'Right'
  set @strDescription = CASE 
  
  WHEN @strDescription LIKE '% ' + @Badword +' %'  THEN REPLACE(@strDescription,' ' +  @Badword + ' ',' ')
  WHEN @strDescription LIKE @Badword +' %' THEN RIGHT(@strDescription, (len(@strDescription)-(len(@Badword)+1)))
  WHEN @strDescription LIKE '% ' + @Badword THEN LEFT(@strDescription, (len(@strDescription)-(len(@Badword)+1)))
  ELSE @strDescription END 
  
  RETURN      @strDescription
  end
  </code></pre>
  
  <hr>
  
  <p>I am new to SQL programming and would like to improve on this. Supposing I wanted to have a table which contained a list of 'bad words' that I wanted to be removed from a string and loop through it when cleaning the description.</p>
  
  <p>I should point out that this process needs to be as efficient as possible as I am dealing with 15 millions records.</p>