title: Strange behavior of LENGTH command - ORACLE
body: <p>I've encountered here an inusited situation that I couldn't understand. Nor the documentation of the functions that I will write about has something to light up this thing.</p>
  
  <p>I've a table with a field <code>titulo varchar2(55)</code>. I'm in Brazil, some of the characters in this field has accents and my goal is to create a similar field without the accents (replaced by the original character as this <code>á</code> became <code>a</code> and so on.).</p>
  
  <p>I could use a bunch of functions to do that as <code>replace</code>, <code>translate</code> and others but I find over the internet one that seams to be more elegant, then I use it. That is where the problem came.</p>
  
  <p>My update code is like:</p>
  
  <pre><code>update myTable 
     set TITULO_URL = replace(
                   utl_raw.cast_to_varchar2(
                             nlssort(titulo, 'nls_sort=binary_ai')
                                           )
                              ,' ','_');
  </code></pre>
  
  <p>As I said the goal is to transform every accented character in its equivalent without the accent plus the spaces character for an <code>_</code></p>
  
  <p>Then I got this error:</p>
  
  <pre><code>ORA-12899: value too large for column 
       "mySchem"."myTable"."TITULO_URL" (actual: 56, maximum: 55)
  </code></pre>
  
  <p>And at first I though maybe those functions are adding some character, let me checkit. I did a select command to get me a row where <code>titulo</code> has 55 characters. </p>
  
  <pre><code>select titulo from myTable where length(titulo) = 55
  </code></pre>
  
  <p>Then I choose a row to do some tests, the row that I choose has this value: <code>'FGHJTÓRYO DE YHJKS DA DGHQÇÃA DE ASGA XCVBGL EASDEÔNASD'</code> (I did change it bit to preserve the data, but the result is the same)</p>
  
  <p>When i do the following select statement that things became weird:</p>
  
  <pre><code>select a, length(a), b, length(b)
    from ( select 'FGHJTÓRYO DE YHJKS DA DGHQÇÃA DE ASGA XCVBGL EASDEÔNASD' a,
                  replace(
                     utl_raw.cast_to_varchar2( 
                                 nlssort('FGHJTÓRYO DE YHJKS DA DGHQÇÃA DE ASGA XCVBGL EASDEÔNASD', 'nls_sort=binary_ai')
                                             )
                         ,' ','_') b
             from dual
         )
  </code></pre>
  
  <p>The result for this sql is (i will put the values one down other for better visualization):</p>
  
  <pre><code>                     a                                       LENGTH(a)
  FGHJTÓRYO DE YHJKS DA DGHQÇÃA DE ASGA XCVBGL EASDEÔNASD        55     
                       b                                       LENGTH(b)
  fghjtoryo_de_yhjks_da_dghqcaa_de_asga_xcvbgl_easdeonasd        56
  </code></pre>
  
  <p>Comparing the two strings one above other there is no difference in size:</p>
  
  <pre><code>FGHJTÓRYO DE YHJKS DA DGHQÇÃA DE ASGA XCVBGL EASDEÔNASD
  fghjtoryo_de_yhjks_da_dghqcaa_de_asga_xcvbgl_easdeonasd
  </code></pre>
  
  <p>I've tested this query on Toad, PLSQL Developer and SQLPLUSW all with the same result. So my question is <strong>Where this LENGTH(b)=56 came from</strong>? I know that it can be something with character set, but I couldn't figure out why. I even tested with the <code>trim</code> command and the result is the same. </p>
  
  <p>Another tests that i did</p>
  
  <ul>
  <li><code>substr(b, 1,55)</code> the result was the same text as above</li>
  <li><code>lenght(trim(b))</code> the result was 56</li>
  <li><code>substr(b,56)</code> the result was empty (no null, no space, just empty)</li>
  </ul>
  
  <p>Suggested by @Sebas:</p>
  
  <ul>
  <li><code>LENGTHB(b)</code> the result was 56</li>
  <li><code>ASCII(substr(b,56))</code></li>
  </ul>
  
  <p>So, again: <strong>Where this LENGTH(b)=56 came from</strong>?</p>
  
  <p>Sorry for the long post and thank you for the ones who get down here (read everything).
  An thanks for the ones who doesn't read anyway :)</p>
  
  <p>Best regards</p>