Precautions for Oracle LPAD/RPAD functions when handling Chinese Characters

First, let's take a look at Oracle's official function definition:

The RPAD function returns an expression, right-padded to a specified length with the specified characters; or, when the expression to be padded is longer than the length specified after padding, only that portion of the expression that fits into the specified length.

The RPAD function fills the specified string with the specified length from the right of the target string. If the length of the string to be filled is greater than the specified length, the target string is intercepted Based on the filling length.

 

RPAD (text-exp, length [, pad-exp])

 

For the explanation of each parameter, let's focus on the parameter length:

Length

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.

When you specify a value for length that is shorter than the length of text-exp, then this function truncates the expression to the specified length.

The bold part indicates the total length of the returned values, just as they are displayed on your terminal screen.Terminal display LengthThe actual length (in bytes) not displayed in the database ). This is particularly evident in Multi-byte supported databases (such as simplified Chinese and AL32UTF8.

The following example is used to understand this sentence:

SQL LENGTHB (RPAD (, LENGTHB (RPAD (, Oracle $

The character set of my database is AL32UTF8, so a Chinese character occupies three bytes. In this example, the LENGTHB function accurately calculates the byte length of the string "oracle", which is 9 bytes, but you will find that, we use the RPAD function to fill the $ symbol behind the string "oracle". When the total length is 10, the result range is "Oracle $ ", it is not the expected value "Oracle $". It also finds that the string length after the truncation is not 10 bytes.

Return to the official definition to explain the returned Length, which depends on the size of the current character displayed on the screen terminal. for Chinese, the screen usually occupies 2 bytes of width:

Therefore, when the RPAD fills these characters in two bytes, the above example is described. This unpredictable situation will undoubtedly cause problems for programs that output a fixed byte width.

 

The following is a solution to this problem:

SQL LENGTHB (RPAD (, LENGTHB (RPAD (, SUBSTRB (RPAD (,,),,Oracle $ SQL

 

In practical application, we can abstract the above method into a function to use:

    rpad2(        SUBSTRB(  RPAD(pad, , pad), ,  rpad2;

Example:

SQL  rpad2(, , ) PADDED_VALUE