title: alternatives to REPLACE on a text or ntext datatype
body: <p>I need to update/replace the data in datatable.column. The table has a field named <code>Content</code>. I'm using the <code>REPLACE</code> function. Since the column datatype is <code>NTEXT</code>, SQL Server doesn't allow me to use the <code>REPLACE</code> function.</p>
  
  <p>I can't change the datatype because this database is 3rd party software table. Changing the datatype will cause the application to fail.</p>
  
  <pre><code>UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
  SET Content = REPLACE(Content,'ABC','DEF') 
  WHERE Content LIKE '%ABC%' 
  </code></pre>
  
  <p>I Receive this error:</p>
  
  <blockquote>
    <p>Msg 8116, Level 16, State 1, Line 1
    Argument data type ntext is invalid for argument 1 of replace function.</p>
  </blockquote>
  
  <ul>
  <li>Can I fix this with T-SQL? Does someone have an example how to read and to loop?</li>
  <li>Since this is onetime conversion, maybe I can change to another type but I'm afraid I'm messing up the data.</li>
  </ul>
  
  <p>There is a primary key field: name: ID  - integer - it's an identity.... So I need to think about this too. Maybe set the Identity to N temporary.</p>
  
  <p>Please advise on how to achieve the REPLACE function?</p>
  
  <p>Approx. 3000 statements need to be updated with a new solution.</p>