oracle函数-LPAD/RPAD

 

基本用法：

lpad函数

函数介绍

lpad函数是 Oracle 数据库函数，lpad函数从左边对 字符串使用指定的字符进行填充。从其字面意思也可以理解，l是left的简写，pad是填充的意思，所以lpad就是从左边填充的意思。

2语法

语法格式如下：

lpad( string, padded_length, [ pad_string ] )

string

准备被填充的字符串；

padded_length

填充之后的 字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;

pad_string

填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。

示例1：

SQL> select lpad('abcde',10,'x') from dual;

LPAD('ABCDE',10,'X')

--------------------

xxxxxabcde

示例2：

SQL> select lpad('abcde',10,'oq') from dual;

LPAD('ABCDE',10,'OQ')

---------------------

oqoqoabcde

示例3：

SQL> select lpad('abcde',2) from dual;

LPAD('ABCDE',2)

---------------

ab

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

与lpad函数对应的是rpad函数：

rpad函数从右边对 字符串使用指定的字符进行填充，语法格式与lpad格式相同：

rpad(string,padded_length,[pad_string])

string

被填充的字符串

padded_length

字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，rpad函数将会把字符串截取成从左到右的n个字符;

pad_string

是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，lpad函数将会在string的右边粘贴空格。

例如：

rpad('tech', 7); 将返回'tech '

rpad('tech', 2); 将返回'te'

rpad('tech', 8, '0'); 将返回'tech0000'

rpad('tech on the net', 15, 'z'); 将返回 'tech on the net'

rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'

 

陷阱处理：

 

首先看下Oracle官方对函数的定义：

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.

RPAD函数将指定的字符串从目标字符串的右侧填充指定长度的字符串，若待填充的字符串的长度大于制定的填充长度，则会根据填充的长度对目标字符串进行截取。

 

函数语法

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

 

对Length填充长度的解释

关于对各个参数的解释，我们重点来看看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.

粗体部分大意为：返回值的总长度正如它们在你的终端屏幕上显示的长度一样，注意，是终端显示的长度，非该字符串在数据库中的实际显示长度（字节）。这在多字节支持的数据库（如简体中文，AL32UTF8等等）中尤为明显。

通过下面的一个示例来理解一下这句话:

SQL> SELECT '甲骨文' ORA_STR,
  2         LENGTHB('甲骨文') ORA_STR_LENGTH,
  3         RPAD('甲骨文', 10, '$') ORA_RPAD_STR,
  4         LENGTHB(RPAD('甲骨文', 10, '$')) ORA_RPAD_STR_LENGTH
  5    FROM dual
  6  ;
 
ORA_STR   ORA_STR_LENGTH ORA_RPAD_STR  ORA_RPAD_STR_LENGTH
--------- -------------- ------------- -------------------
甲骨文                 9 甲骨文$$$$                     13
 
SQL> 

我的数据库的字符集是AL32UTF8，所以一个中文字符占3个字节。在这个例子中，LENGTHB函数对字符串“甲骨文”的字节长度计算很准确，为9个字节，但是你会发现，我们用RPAD函数向字符串”甲骨文“后面填充$符号，总长度为10时，结果范围的却是”甲骨文$$$$"，并不是我们期望得到的值"甲骨文$"，同时也会发现，截取完后的字符串长度也并不是10个字节。

我们回到官方定义中对于返回长度Length的解释，这个长度取决于当前字符在屏幕终端显示时所占据的大小，对于中文来说，通常在屏幕上占据的是2个字节的宽度：

因此RPAD在进行填充的时候对这些字符按照两个字节去处理，所以出现了上述示例所描述的情况。这种不可预知（Sometimes）的情况对于那些输出固定字节宽度的程序来说无疑会产生问题。

 

解决方法

下面是针对这种问题的解决方法：

SQL> SELECT '甲骨文' ORA_STR,
  2         LENGTHB('甲骨文') ORA_STR_LENGTH,
  3         RPAD('甲骨文', 10, '$') ORA_RPAD_STR,
  4         LENGTHB(RPAD('甲骨文', 10, '$')) ORA_RPAD_STR_LENGTH,
  5         -- 解决方法
  6         SUBSTRB('甲骨文' || RPAD('$', 10, '$'), 1, 10) SOLUTION
  7    FROM dual
  8  ;
 
ORA_STR   ORA_STR_LENGTH ORA_RPAD_STR  ORA_RPAD_STR_LENGTH SOLUTION
--------- -------------- ------------- ------------------- ----------
甲骨文                 9 甲骨文$$$$                     13 甲骨文$
 
SQL> 

 

在实际应用中，我们可以将上述方法抽象为一个函数来使用：

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;

使用范例：

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

 

 参考链接

1. Oracle Bug? RPAD of Japanese (kanji) character in Oracle 10gR2 UTF8 database
2. Issue with LPAD/RPAD when using with Japanese data
3. Oracle Documentation - RPAD

转载于:https://www.cnblogs.com/huning188/p/7413117.html