Oracle function-lpad/rpad

Basic usage:

Lpad function

Function IntroductionThe Lpad function is an Oracle database function, and the Lpad function populates the string with the specified character from the left. From its literal meaning can also be understood, L is the left shorthand, pad is the meaning of padding, so the lpad is the meaning of the padding.2 Syntax The syntax format is as follows:Lpad (String, Padded_length, [pad_string]) stringPrepares the string to be filled; Padded_lengthThe length of the string after padding, that is, the length of the string returned by the function, if the number is shorter than the length of the original string, the Lpad function will intercept the string as a left-to-right n character; pad_stringPadding string, is an optional argument, this string is to be pasted to the left of string, if this parameter is not written, the Lpad function will paste a space on the left side of the string. Example 1:Sql> Select Lpad (' ABCDE ', ten, ' X ') from dual; Lpad (' ABCDE ', ten, ' X ')--------------------XXXXXABCDE Example 2:Sql> Select Lpad (' ABCDE ', ten, ' OQ ') from dual; Lpad (' ABCDE ', ten, ' OQ ')---------------------OQOQOABCDE Example 3:Sql> Select Lpad (' ABCDE ', 2) from dual; Lpad (' ABCDE ', 2)---------------ab~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ corresponding to the Lpad function is the Rpad function: The Rpad function fills the string with the specified character from the right, and the syntax format is the same as the Lpad format: Rpad (string,padded_length,[pad_string]) String is filled with the length of the padded_length character, is the number of strings returned, if the number is shorter than the length of the original string, the Rpad function will intercept the string as left-to-right n characters; pad_string is an optional parameter. This string is to be pasted to the right of the string, and if this argument is not written, the Lpad function will paste a space on the right side of the string. For example: Rpad (' Tech ', 7); will return ' tech ' rpad (' tech ', 2); will return ' Te ' rpad (' tech ', 8, ' 0 '); will return ' tech0000 ' Rpad (' Tech on the net ', ' Z '); Will return ' Tech on the net ' Rpad (' Tech on the net ', ' Z '); Will return ' tech on the Netz ' Trap Handling:

First look at Oracle's definition of the function:

The Rpad function returns an expression, right-padded to a specified length with the specified characters; Or, when the expression was padded was longer than the length specified after padding, only that portion of the Expressio n that fits into the specified length.

The Rpad function fills the specified string from the right side of the target string with a string of the specified length, and if the length of the string to be filled is greater than the length of the fill, the target string is intercepted based on the length of the fill.

function syntax

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

Explanation of length padding lengths

For an explanation of each parameter, we'll focus on the length parameter:

Length

The total length of the return value as it was displayed on your terminalscreens. In the very 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 CHARACTE RS in the string.

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

The bold part of the main idea is: The total length of the return value as they appear on your terminal screen the same length, note that is the length of the terminal display , not the actual length of the string in the database display (bytes). This is particularly noticeable in multi-byte supported databases such as Simplified Chinese, al32utf8, and so on.

Take a look at the following example to understand the sentence:

SQL> SELECT ‘Oracle’ ORA_STR,
   2 LENGTHB (‘Oracle’) ORA_STR_LENGTH,
   3 RPAD (‘Oracle’, 10, ‘$’) ORA_RPAD_STR,
   4 LENGTHB (RPAD (‘Oracle’, 10, ‘$‘)) ORA_RPAD_STR_LENGTH
   5 FROM dual
   6;
 
ORA_STR ORA_STR_LENGTH ORA_RPAD_STR ORA_RPAD_STR_LENGTH
--------- -------------- ------------- -------------- -----
Oracle 9 Oracle $$$$ 13
 
SQL>

The character set of my database is Al32utf8, so a Chinese characters account for 3 bytes. In this example, the LENGTHB function calculates the byte length of the string "Oracle" very accurately, 9 bytes, but you will find that we use the Rpad function to fill the string "Oracle" after the $ symbol, the total length is 10 o'clock, the result range is "Oracle $", is not what we expect to get the value "Oracle $", but also found that the length of the truncated string is not 10 bytes.

We go back to the official definition of the length of return lengths, depending on the size of the current character in the screen terminal display, for Chinese, usually on the screen occupies a width of 2 bytes:

As a result, rpad the characters according to two bytes when filling, so the case described in the example above appears. This unpredictable (sometimes) situation will undoubtedly cause problems for programs that output fixed byte widths.

Workaround

Here's a workaround for this problem:

SQL> SELECT ‘Oracle’ ORA_STR,
   2 LENGTHB (‘Oracle’) ORA_STR_LENGTH,
   3 RPAD (‘Oracle’, 10, ‘$’) ORA_RPAD_STR,
   4 LENGTHB (RPAD (‘Oracle’, 10, ‘$‘)) ORA_RPAD_STR_LENGTH,
   5-Solution
   6 SUBSTRB (‘Oracle’ || RPAD (‘$‘, 10, ‘$‘), 1, 10) SOLUTION
   7 FROM dual
   8  ;
 
ORA_STR ORA_STR_LENGTH ORA_RPAD_STR ORA_RPAD_STR_LENGTH SOLUTION
--------- -------------- ------------- -------------- ----- ----------
Oracle 9 Oracle $$$$ 13 Oracle $
 
SQL>

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

CREATE OR REPLACE 
FUNCTION rpad2(str IN VARCHAR2,
               len IN PLS_INTEGER,
               pad IN VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  RETURN SUBSTRB(str || RPAD(pad, len, pad), 1, len);
END rpad2;

Examples of Use:

SQL> SELECT rpad2(‘甲骨文‘, 10, ‘$‘) PADDED_VALUE FROM DUAL;
 
PADDED_VALUE
-----------------------
Oracle$
 
SQL> 

Reference Links

1. Oracle Bug? Rpad of Japanese (kanji) character in Oracle 10gr2 UTF8 database
2. Issue with Lpad/rpad if using with Japanese data
3. Oracle Documentation-rpad

Oracle function-lpad/rpad