Oracle LPAD/RPAD函数在处理中文时的注意事项

deci

首先看下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>

参考链接

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

yiwai

个性签名：我还是象牙塔的孩子、能不能不离开