title: Using T-SQL REPLACE with wildcard lookup to replace field values
body: <p>I have table with schema:</p>
  
  <pre><code>CREATE TABLE InvestorDetails
  (
      [ID] INT PRIMARY KEY,
      [Created] DATETIME,
      [Updated] DATETIME,
      [ToEmail] VARCHAR(MAX)
  )
  </code></pre>
  
  <p>Records in InvestorDetails look like the following:</p>
  
  <pre><code>1, 2017-01-01, 2017-02-02, ames@gmail.com;mark@gmail.com;tom@gmail.com;mitchel@gmail.com
  2, 2017,01-02, 2017-02-02, ben@gmail.com;michael@gmail.com;mark@gmail.com
  3, 2017-02-02, 2017,02-03, test@gmail.com;matthew@gmail.com;dan@gmail.com;jay@gmail.com;mark@gmail.com
  </code></pre>
  
  <p>I want to write a query that will go through and update each record in the table that has 'mark@gmail.com' in field 'ToEmail' with 'markTest@gmail.com' </p>
  
  <p>So for example in record 1 above, I want it to look like:</p>
  
  <pre><code>1, 2017-01-01, 2017-02-02, ames@gmail.com;markTest@gmail.com;tom@gmail.com;mitchel@gmail.com
  </code></pre>
  
  <p>I can find the records with mark@gmail.com in 'EmailRecipients'using the following query:</p>
  
  <pre><code>SELECT I.ID, I.EmailRecipients
  FROM InvestorDetails I
  WHERE I.EmailRecipients LIKE '%mark@gmail.com%' 
  </code></pre>
  
  <p>The tricky part is ensuring only the mark@gmail.com is changed, and the rest remains. I believe this can be achieved with TSQL REPLACE, but I'm having trouble writing the SQL, particularly what to use as the string_pattern and string_replacement parameters.</p>
  
  <p>Any help is greatly appreciated!</p>
  
  <p>Thanks!</p>