title: SQL Server: replace multiple occurrences of a word with one
body: <p>I have a SQL Server table with a column <code>Event</code> which contains values like this:</p>
  
  <pre><code>[Event]
  ----------------------------------------
  Payment stopped, Claim, Claim, Claim
  Claim
  Claim, Claim, Claim
  Claim, Claim, Payment stopped, Case Closed
  </code></pre>
  
  <p>Now the user wants to view this column which has the summary count of the word 'Claim', so that the below column would look like</p>
  
  <pre><code>[Event]
  -------------------------------------
  Payment stopped, 3 Claims
  Claim
  3 Claims
  2 Claims, Payment stopped, Case Closed
  </code></pre>
  
  <p>The word 'Claim' could present up to 400 times, they don't mind keeping the count in the starting of the value (3 Claims, Payment stopped), or end of the value (Payment stopped, 3 Claims). I have an udf that could get the count of this word, but removing the commas, spaces seems really difficult. </p>
  
  <p>Is there a way to do this (with or without udf)? I am using SQL Server 2008.</p>