Oracle LPAD/RPAD function issue in AL32UTF8

Question

The NLS_CHARACTERSET is AL23UTF8 in my Oracle database. I have problem when using the RPAD function:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
Connected as apps@UATSED

SQL> 
SQL> SELECT '甲骨文' ORACLE, LENGTHB('甲骨文') ORACLE_LENGTH,
  2         RPAD('甲骨文', 10, '$') ORA_RPAD, LENGTHB(RPAD('甲骨文', 10, '$')) ORA_RPAD_LENGTH
  3    FROM dual
  4  ;

ORACLE    ORACLE_LENGTH ORA_RPAD      ORA_RPAD_LENGTH
--------- ------------- ------------- ---------------
甲骨文                9 甲骨文$$$$                 13

SQL> 

We know a Chinese character take 3 bytes in AL32UTF8 encoding method. Therefore in my example, the lengthb function returns the correct result. But we I use RPAD function to pad more spaces with $, it took 2 bytes for one Chinese character, not 3 bytes. So when i pad 10 bytes in total, it filled 4 $ signs for me.

My question is why RPAD function don't follow the way like lengthb?

Answer 1

As the request of @jonearles , I copyed my solution on comment section as single answer here to help people to solve this problem.

Hi guys, I've got the reason and workarounds by searching from Google. Here is the explanation from Oracle documentation: "The total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string." And workarounds depend on the actual display length in your screen (View text in monospaced fonts)

Workarounds

-- 1) 
SELECT RPAD('甲骨文', 10 - LENGTHC('甲骨文'), '$') FROM DUAL; 
-- 2) Recomended!
SELECT SUBSTRB('甲骨文' || RPAD('$', 10, '$'), 1, 10) FROM DUAL;