title: How to get rid of NUL characters in Oracle database?
body: <p><strong>The database</strong>:
  I have a Oracle11g database with a table that contains about 10 million rows and has about 40 columns. The data originates from punched card age and has been transformed from one Oracle version to the next several times. This is a live production database and in somewhat continuous use. But it's not so critical (especially during night time) that I could not strain it with expensive queries and updates. So that's ok.</p>
  
  <p>edit: Charset is AL32UTF8.</p>
  
  <p><strong>The problem</strong>:
  I noticed that some columns contain NUL characters. I have found values that consist of 1-4 NUL characters, but there can be in theory as many of them than the number of chars available for that column. I want to get rid of the NUL characters. If there are only NUL characters in the value, I want to change the column's value to SQL NULL. If there are NUL characters between other characters (I haven't though seen any cases) I want to remove them (replace with "").</p>
  
  <p><strong>What I have tried</strong>:</p>
  
  <p>I have noticed that <code>select rawtohex(mycolumn) from mytable</code> returns for example '000000' (3 NUL characters). </p>
  
  <p><code>select rawtohex('A') from dual;</code> returns '61'</p>
  
  <p><code>select rawtohex('Ä') from dual;</code> returns 'C385'.</p>
  
  <p>I have explored finding NUL characters like this:</p>
  
  <pre><code>SELECT DISTINCT mycolumn 
  FROM mytable
  WHERE rawtohex(mycolumn) LIKE '%00%;'
  </code></pre>
  
  <p>So far I haven't been able to find any columns that would contain any characters that's rawtohex would contain '00', except columns where there are only NUL characters. So it looks like using <code>LIKE '%00%'</code> is safe. But I'm uncertain of oracle implementation of rawtohex and what kind of hex coding it uses.</p>
  
  <p>So... While I continue exploring this path and eventually write a script to fix the garbage, I'm asking if anyone has encountered this job before and how did you solve it. :)</p>