SELECT LENGTH('*'||CHR(255)||CHR(255)||'$')
FROM DUAL;
This query gives the output of 2 instead of 4 ?
But
SELECT LENGTH(CHR(255)||CHR(255))
FROM DUAL;
This query gives the output as null. That means the string represented by CHR(255)||CHR(255) is an empty string of length 0.
How does that CHR(255) influence the length ?
The chr(255) in your concatenated string is treated as null, which has no length, so only the other non-null characters are counted - hence it gets 2 rather than 4.
ASCII doesn't really go up to 255, and you aren't really dealing with ASCII. Your database character set is (presumably) AL32UTF8, which is a multibyte character set. From FileFormat.Info's summary:
For any character equal to or below 127 (hex 0x7F), the UTF-8 representation is one byte. It is just the lowest 7 bits of the full unicode value. This is also the same as the ASCII value.
For characters equal to or below 2047 (hex 0x07FF), the UTF-8 representation is spread across two bytes. The first byte will have the two high bits set and the third bit clear (i.e. 0xC2 to 0xDF). The second byte will have the top bit set and the second bit clear (i.e. 0x80 to 0xBF).
And from the documentation for chr():
For multibyte character sets, n must resolve to one entire code point. Invalid code points are not validated, and the result of specifying invalid code points is indeterminate.
For UTF8 there is no entire code point 255/FF, so chr(255) is not valid. In fact, according to the spec, there are no code points with an FF octet.
You might expect it to be rendered as 'ÿ'; if you use an encoding where it is valid like AL16UTF16:
select chr(255 using nchar_cs), dump(chr(255 using nchar_cs), 1016) as chr_dump,
unistr('\00ff'), dump(unistr('\00ff'), 1016) as unistr_dump
from dual;
C CHR_DUMP U UNISTR_DUMP
- ---------------------------------------- - --------------------------------------------------
ÿ Typ=1 Len=2 CharacterSet=AL16UTF16: 0,ff ÿ Typ=1 Len=2 CharacterSet=AL16UTF16: 0,ff
but because of the way UTF8 is encoded that (and everything above 127) is actually multiple bytes, C3BF.
Where it gets slightly more interesting is how Oracle is handling that invalid character. On its own you can see that it exists and is invalid, but when it's concatenated with another (valid or invalid) character it's essentially ignored:
with t (descr, str) as (
select 'chr(255)', chr(255) from dual
union all select 'chr(255)||chr(255)', chr(255)||chr(255) from dual
union all select q'['*'||chr(255)]', '*'||chr(255) from dual
union all select q'[chr(255)||'$']', chr(255)||'$' from dual
union all select q'['*'||chr(255)||'$']', '*'||chr(255)||'$' from dual
union all select q'['*'||chr(255)||'$'||chr(255)]', '*'||chr(255)||'$'||chr(255) from dual
union all select q'[chr(255)||'*'||chr(255)||'$']', chr(255)||'*'||chr(255)||'$' from dual
union all select q'['*'||chr(255)||chr(255)||'$']', '*'||chr(255)||chr(255)||'$' from dual
union all select q'['ÿ']', 'ÿ' from dual
union all select 'chr(127)||chr(127)', chr(127)||chr(127) from dual
union all select 'chr(127)||chr(128)', chr(127)||chr(128) from dual
union all select 'chr(128)||chr(127)', chr(128)||chr(127) from dual
union all select 'chr(128)||chr(128)', chr(128)||chr(128) from dual
)
select descr, str, dump(str, 1016) as str_dump, length(str) as str_length
from t;
DESCR ST STR_DUMP STR_LENGTH
---------------------------- -- -------------------------------------------------- ----------
chr(255) ? Typ=1 Len=1 CharacterSet=AL32UTF8: ff 1
chr(255)||chr(255) NULL
'*'||chr(255) * Typ=1 Len=1 CharacterSet=AL32UTF8: 2a 1
chr(255)||'$' $ Typ=1 Len=1 CharacterSet=AL32UTF8: 24 1
'*'||chr(255)||'$' *$ Typ=1 Len=2 CharacterSet=AL32UTF8: 2a,24 2
'*'||chr(255)||'$'||chr(255) *$ Typ=1 Len=2 CharacterSet=AL32UTF8: 2a,24 2
chr(255)||'*'||chr(255)||'$' *$ Typ=1 Len=2 CharacterSet=AL32UTF8: 2a,24 2
'*'||chr(255)||chr(255)||'$' *$ Typ=1 Len=2 CharacterSet=AL32UTF8: 2a,24 2
'ÿ' ÿ Typ=1 Len=2 CharacterSet=AL32UTF8: c3,bf 1
chr(127)||chr(127) Typ=1 Len=2 CharacterSet=AL32UTF8: 7f,7f 2
chr(127)||chr(128) Typ=1 Len=1 CharacterSet=AL32UTF8: 7f 1
chr(128)||chr(127) Typ=1 Len=1 CharacterSet=AL32UTF8: 7f 1
chr(128)||chr(128) NULL
The last few examples are showing that this isn't specific to 255, it's anything above 127 that's an issue, because UTF8 jumps from 127/7F (still a single byte) to 128/C280 (two bytes). (You can see the jump here, for instance.)
And here's a quick demo that concatenating any invalid character formed using 128-255 is is treated as null whatever it is concatenated with:
with t (n) as (
select level from dual connect by level <= 255
)
select count(*), min(t1.n), max(t1.n), min(t1.n), max(t2.n)
from t t1
cross join t t2
where chr(t1.n)||chr(t2.n) is null
order by t1.n, t2.n;
COUNT(*) MIN(T1.N) MAX(T1.N) MIN(T1.N) MAX(T2.N)
---------- ---------- ---------- ---------- ----------
16384 128 255 128 255
255 is not a valid isolated byte in utf-8 encoding, but it is indeed a valid unicode code point.
The question is what does chr(255) mean in Oracle? Is it the 255th unicode code point? Or is it a reference to the 0x11111111 byte. Of course it depends on the character set in question.
select
length (chr(255) || chr(255)), -- NULL
lengthb(chr(255) || chr(255)), -- NULL
length (nchr(255)||nchr(255)), -- 2 valid unicode characters
lengthb(nchr(255)||nchr(255)) -- 4 bytes (in AL32UTF8)
from dual;
To write portable sql statements, I would strongly recommend using CHR only on (0..127), and never write CHR(255) in queries. Stick to ASCII with CHR or move to NCHR when unicode characters are needed.