Problem with Oracle LPAD / RPAD in AL32UTF8

Question

NLS_CHARACTERSET is AL23UTF8 in my Oracle database. I have a 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 that the Chinese character takes 3 bytes in the AL32UTF8 encoding method. Therefore, in my example, the lengthb function returns the correct result. But we use the RPAD function to place more spaces with $, 2 Chinese bytes are required for one Chinese character, not 3 bytes. Therefore, when I type 10 bytes in total, it filled in 4-digit characters for me.

My question is: why does the RPAD function not match the lengthb method?

Answer 1

As a @jonearles request, I copied my solution in the comments section as a single answer here to help people solve this problem.

Hi guys, I have a reason and workarounds when searching on Google. Here is an explanation from the Oracle documentation: “The total length of the return value as it appears on the terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the length of the character string may differ from the number of characters in the string. " And workarounds depend on the actual length of the display on your screen (viewing text in monospaced fonts)

Bypass

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

Answer 2

This is an Oracle error. The cause of the error is: Each $ is one byte, but each Chinese character is 3 bytes. When RPAD works in this version, it just calculates 3 Chinese + 10 $ = 13 bytes. When it displays 13 bytes - it should cut some $. You may open an error with Oracle.