Strange behavior of LENGTH command - ORACLE

Question

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.

I've a table with a field titulo varchar2(55). 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 á became a and so on.).

I could use a bunch of functions to do that as replace, translate 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.

My update code is like:

update myTable 
   set TITULO_URL = replace(
                 utl_raw.cast_to_varchar2(
                           nlssort(titulo, 'nls_sort=binary_ai')
                                         )
                            ,' ','_');

As I said the goal is to transform every accented character in its equivalent without the accent plus the spaces character for an _

Then I got this error:

ORA-12899: value too large for column 
     "mySchem"."myTable"."TITULO_URL" (actual: 56, maximum: 55)

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 titulo has 55 characters.

select titulo from myTable where length(titulo) = 55

Then I choose a row to do some tests, the row that I choose has this value: 'FGHJTÓRYO DE YHJKS DA DGHQÇÃA DE ASGA XCVBGL EASDEÔNASD' (I did change it bit to preserve the data, but the result is the same)

When i do the following select statement that things became weird:

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
       )

The result for this sql is (i will put the values one down other for better visualization):

                     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

Comparing the two strings one above other there is no difference in size:

FGHJTÓRYO DE YHJKS DA DGHQÇÃA DE ASGA XCVBGL EASDEÔNASD
fghjtoryo_de_yhjks_da_dghqcaa_de_asga_xcvbgl_easdeonasd

I've tested this query on Toad, PLSQL Developer and SQLPLUSW all with the same result. So my question is Where this LENGTH(b)=56 came from? I know that it can be something with character set, but I couldn't figure out why. I even tested with the trim command and the result is the same.

Another tests that i did

• substr(b, 1,55) the result was the same text as above
• lenght(trim(b)) the result was 56
• substr(b,56) the result was empty (no null, no space, just empty)

Suggested by @Sebas:

• LENGTHB(b) the result was 56
• ASCII(substr(b,56))

So, again: Where this LENGTH(b)=56 came from?

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 :)

Best regards

Answer 1

The 'nlssort' function's documentation does not state that the output string will be a normalization of the input string, or that they will have same length. The purpose of the function is to return data that can be used to sort the input string.

See http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions113.htm#SQLRF51561

It is tempting to use it to normalize your string since apparently it works, but you are gambling here...

Heck, it could even yield a LENGTH(b)=200 and still be doing what it is supposed to do :)