title: Remove accents from string in Oracle
body: <p>When trying to remove all accents from a string in Oracle using the techniques described in this stackoverflow answer: <a href="https://stackoverflow.com/questions/28837263/how-replace-accented-letter-in-a-varchar2-column-in-oracle">how replace accented letter in a varchar2 column in oracle</a> I’m getting mixed results.</p>
  
  <pre><code>select CONVERT('JUAN ROMÄN', 'US7ASCII') from dual;
  </code></pre>
  
  <p>Returns the original string but replaces characters with for example ñ by a question mark (probably because of the chosen charset - tests with different charsets led to different results).</p>
  
  <p>Using the following technique:</p>
  
  <pre><code>select utl_raw.cast_to_varchar2(nlssort(NAME_USER, 'nls_sort=binary_ai')) from YOUR_TABLE;
  </code></pre>
  
  <p>Returns the complete string but also places a NUL value at the end of the string.</p>
  
  <p>Is there a characterset that I can use with Spanish accents to get a correct result (the original string with the different accents removed); is there a way to avoid the NUL value in the utl_raw.cast_to_varchar2 technique?</p>
  
  <p>Based on the comments the the replace char(0) seems to remove the NUL value. For example</p>
  
  <pre><code>select
     upper(utl_raw.cast_to_varchar2((nlssort('this is áà ñew test','nls_sort=binary_ai')))) as test,
     replace(upper(utl_raw.cast_to_varchar2((nlssort('this is áà ñew test','nls_sort=binary_ai')))),chr(0),'') as test2
  from dual;
  </code></pre>
  
  <p>If possible I would however to have a more 'straightforward/simpler' solution.</p>