title: Mass Find and Replace control characters in SQL
body: <p>ASCII control characters are finding their way in to databases I maintain at a semi-frequent basis. While I plan to implement the means to prevent them from being <code>INSERT</code>ed  in to SQL I have to first deal with characters <em>already</em> present in the database <em>first</em>.</p>
  
  <p>For starters I know that it's possible to do a <strong>find and replace</strong> using SQL...</p>
  
  <pre><code>UPDATE [table_name] 
  SET [field_name]=REPLACE([field_name],'[string_to_find]','[string_to_replace]');
  </code></pre>
  
  <p>PHP allows you to use an array to replace multiple items with something else...</p>
  
  <pre><code>$result = str_ireplace(array('1','2','3','4','5'),'0',$my_string);
  </code></pre>
  
  <p>So instead of lazy SQL queries in loops is there a way we can contruct an SQL query to replace multiple ASCII control characters?</p>
  
  <p>Also very important is <strong>how</strong> we replace those control characters (e.g. as dec, oct, hex)?</p>
  
  <p>Also I've read that <strong>carriage return</strong> is part of the control characters 0~31 so obviously we don't want to remove those to make sure line-breaks are retained unless I'm missing something on that?</p>
  
  <p>Here is a reference page I'm going by right now...</p>
  
  <p><a href="http://www.ascii-code.com/" rel="nofollow noreferrer">http://www.ascii-code.com/</a></p>
  
  <hr>
  
  <p>I code XHTML as application/xhtml+xml and tested the following as <strong>example1.xhtml</strong> and open EXPLICITLY in <strong>Firefox</strong> with <strong>NO BYTE ORDER MARK (BOM)</strong>...</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"&gt;
  &lt;html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"&gt;
  &lt;head&gt;
  &lt;/head&gt;
  
  &lt;body&gt;
  
  &lt;div&gt;
  &lt;p&gt;&amp;#000;&amp;#001;&amp;#002;&amp;#003;&amp;#004;&amp;#005;&amp;#006;&amp;#007;&amp;#008;&amp;#009;
  &amp;#010;&amp;#011;&amp;#012;&amp;#013;&amp;#014;&amp;#015;&amp;#016;&amp;#017;&amp;#018;&amp;#019;
  &amp;#020;&amp;#021;&amp;#022;&amp;#023;&amp;#024;&amp;#025;&amp;#026;&amp;#027;&amp;#028;&amp;#029;
  &amp;#030;&amp;#031;&lt;/p&gt;
  &lt;/div&gt;
  
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>The following are the only characters that can be used without creating malformed XML, save as <strong>example2.xhtml</strong> and open EXPLICITLY in <strong>Firefox</strong> with <strong>NO BYTE ORDER MARK (BOM)</strong>...</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"&gt;
  &lt;html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"&gt;
  &lt;head&gt;
  &lt;/head&gt;
  
  &lt;body&gt;
  
  &lt;div&gt;
  &lt;p&gt;&amp;#009;&amp;#010;&amp;#013;&lt;/p&gt;
  &lt;/div&gt;
  
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>Those working characters that we do <em>not</em> want to strip in SQL are...</p>
  
  <ul>
  <li><p>09 - Horizontal Tab</p></li>
  <li><p>10 - Line Feed</p></li>
  <li><p>13 - Carriage Return</p></li>
  </ul>