关于oracle 10g在utf8字符集下的rpad函数的疑惑

Yong Huang

Why not use only one character in the string so the test is easier for everybody to read? And use dump() function to check the actual stored data.

Yong Huang

sqysl

YONGHUAN说的是，我现在把字符‘⑥'在ZHS16GBK和UTF8下的测试情况贴一下，我觉得除了该字符在两种字符集下rpad应用中存在莫名差异外，其他没什么异常情况发现，该字符在两种字符集下，都是按照多字节存储的，而在UTF8下RPAD函数中却只被当做一个占位符，确实有点费解，大家可以看一下，红色表示的地方是指大家要注意的地方，开始是ZHS16GBK字符集下的，然后是UTF8下的，大家一起讨论：

Zhs16gbk：
SQL> SELECT RPAD('⑥',3,'-') FROM DUAL;
RPA
---
⑥-

SQL> SELECT '⑥' FROM DUAL;
'
--
⑥

SQL> SELECT LENGTH('⑥') FROM DUAL;
LENGTH('⑥')
------------
           1

SQL> SELECT LENGTHB('⑥') FROM DUAL;
LENGTHB('⑥')
-------------
            2

SQL> SELECT CONVERT('⑥','UTF8','ZHS16GBK') FROM DUAL;
SP2-0784: 返回的以 0xA5 开头的字符无效或不完整

SQL> SELECT LENGTH(CONVERT('⑥','UTF8','ZHS16GBK')) FROM DUAL;
LENGTH(CONVERT('⑥','UTF8','ZHS16GBK'))
---------------------------------------
                                      1

SQL> SELECT LENGTHB(CONVERT('⑥','UTF8','ZHS16GBK')) FROM DUAL;
LENGTHB(CONVERT('⑥','UTF8','ZHS16GBK'))
----------------------------------------
                                       3

SQL> select dump('⑥',1016) from dual;
DUMP('⑥',1016)
-----------------------------------------
Typ=96 Len=2 CharacterSet=ZHS16GBK: a2,de

SQL> select dump(CONVERT('⑥', 'UTF8', 'zhs16gbk'),1016) from dual;
DUMP(CONVERT('⑥','UTF8','ZHS16GBK'),1016)
-------------------------------------------
Typ=1 Len=3 CharacterSet=ZHS16GBK: e2,91,a5

Utf8：
SQL> SELECT RPAD('⑥',3,'-') FROM DUAL;
RPAD('⑥',
----------
⑥--

SQL> select '⑥' from dual;
'⑥'
------
⑥

SQL> SELECT LENGTH('⑥') FROM DUAL;
LENGTH('⑥')
------------
           1

SQL> SELECT LENGTHB('⑥') FROM DUAL;
LENGTHB('⑥')
-------------
            3

SQL>select convert('⑥','zhs16gbk','UTF8') from dual;
ERROR:
ORA-29275: 部分多字节字符

未选定行

SQL> select LENGTH(convert('⑥','zhs16gbk','UTF8')) from dual;
LENGTH(CONVERT('⑥','ZHS16GBK','UTF8'))
---------------------------------------
                                      1

SQL> select LENGTHB(convert('⑥','zhs16gbk','UTF8')) from dual;
LENGTHB(CONVERT('⑥','ZHS16GBK','UTF8'))
----------------------------------------
                                       2

SQL> select dump('⑥',1016) from dual;
DUMP('⑥',1016)
--------------------------------------------------------------------------------
Typ=96 Len=3 CharacterSet=UTF8: e2,91,a5

SQL> select dump(CONVERT('⑥', 'zhs16gbk', 'UTF8'), 1016) from dual;
DUMP(CONVERT('⑥','ZHS16GBK','UTF8'),1016)
------------------------------------------------------------------------
Typ=1 Len=2 CharacterSet=UTF8: a2,de

注意：type=1，代表VARCHAR2或NVARCHAR2；
      Type=96，代表CHAR或NCHAR；
见：Oracle® Database SQL Language Reference11g Release 1 (11.1) B28286-02   
P40 table2-1

ClearWindxsw

1. 
   
2. Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
   
3. Connected as ******
   
4. 
   
5. SQL>
   
6. SQL> --字符集
   
7. SQL> SELECT USERENV('language') LANG  FROM DUAL;
   
8. 
   
9. LANG
   
10. ----------------------------------------------------
    
11. SIMPLIFIED CHINESE_CHINA.UTF8
    
12. SQL> --version
    
13. SQL> SELECT VERSION FROM V$INSTANCE;
    
14. 
    
15. VERSION
    
16. -----------------
    
17. 10.2.0.4.0
    
18. SQL> ------默认情况下的特殊字符和'集'的显示及DUMP-----------
    
19. SQL> --默认
    
20. SQL> SELECT 'default' REMARK, '⑥' STR, LENGTH('⑥') L_STR,
    
21.   2         LENGTHB('⑥') LB_STR, DUMP('⑥', 1016) D_STR
    
22.   3    FROM DUAL
    
23.   4  UNION
    
24.   5  SELECT 'default' REMARK, '集' STR, LENGTH('集') L_STR,
    
25.   6         LENGTHB('集') LB_STR, DUMP('集', 1016) D_STR
    
26.   7    FROM DUAL
    
27.   8  UNION --联合一下方便查看
    
28.   9  --convert之后的
    
29. 10  SELECT 'convert' REMARK,
    
30. 11         CONVERT('⑥', 'zhs16gbk', 'UTF8') STR,
    
31. 12         LENGTH(CONVERT('⑥', 'zhs16gbk', 'UTF8')) L_STR,
    
32. 13         LENGTHB(CONVERT('⑥', 'zhs16gbk', 'UTF8')) LB_STR,
    
33. 14         DUMP(CONVERT('⑥', 'zhs16gbk', 'UTF8'), 1016) D_STR
    
34. 15    FROM DUAL
    
35. 16  UNION
    
36. 17  SELECT 'convert' REMARK,
    
37. 18         CONVERT('集', 'zhs16gbk', 'UTF8') STR,
    
38. 19         LENGTH(CONVERT('集', 'zhs16gbk', 'UTF8')) L_STR,
    
39. 20         LENGTHB(CONVERT('集', 'zhs16gbk', 'UTF8')) LB_STR,
    
40. 21         DUMP(CONVERT('集', 'zhs16gbk', 'UTF8'), 1016) D_STR
    
41. 22    FROM DUAL;
    
42. 
    
43. REMARK  STR      L_STR     LB_STR D_STR
    
44. ------- --- ---------- ---------- ----------------------------------------
    
45. convert ⑥           1          2 Typ=1 Len=2 CharacterSet=UTF8: a2,de
    
46. convert 集           2          2 Typ=1 Len=2 CharacterSet=UTF8: bc,af
    
47. default ⑥           1          3 Typ=96 Len=3 CharacterSet=UTF8: e2,91,a5
    
48. default 集           1          3 Typ=96 Len=3 CharacterSet=UTF8: e9,9b,86
    
49. SQL> -------------对RPAD进行的测试-------------
    
50. SQL> SELECT 'default' REMARK, --默认情况下的RPAD
    
51.   2         RPAD('⑥', 3, '-') STR,
    
52.   3         LENGTH(RPAD('⑥', 3, '-')) L_STR,
    
53.   4         LENGTHB(RPAD('⑥', 3, '-')) LB_STR,
    
54.   5         RPAD('集', 3, '-') ZH,
    
55.   6         LENGTH(RPAD('集', 3, '-')) L_ZH,
    
56.   7         LENGTHB(RPAD('集', 3, '-')) LB_ZH
    
57.   8    FROM DUAL
    
58.   9  UNION
    
59. 10  SELECT 'convert' REMARK, --convert后的RPAD
    
60. 11         RPAD(CONVERT('⑥', 'zhs16gbk', 'UTF8'), 3, '-') STR,
    
61. 12         LENGTH(RPAD(CONVERT('⑥', 'zhs16gbk', 'UTF8'), 3, '-')) L_STR,
    
62. 13         LENGTHB(RPAD(CONVERT('⑥', 'zhs16gbk', 'UTF8'), 3, '-')) LB_STR,
    
63. 14         RPAD(CONVERT('集', 'zhs16gbk', 'UTF8'), 3, '-') ZH,
    
64. 15         LENGTH(RPAD(CONVERT('集', 'zhs16gbk', 'UTF8'), 3, '-')) L_ZH,
    
65. 16         LENGTHB(RPAD(CONVERT('集', 'zhs16gbk', 'UTF8'), 3, '-')) LB_ZH
    
66. 17    FROM DUAL;
    
67. 
    
68. REMARK  STR        L_STR     LB_STR ZH         L_ZH      LB_ZH
    
69. ------- ----- ---------- ---------- ---- ---------- ----------
    
70. convert ?-             3          3 集-           3          3
    
71. default ⑥--           3          5 集-           2          4
    
72. 
    
73. SQL>
    

复制代码

这是自己在UTF8字符集下做的简化测试
结果：
1、把⑥转换一下，总长度是满足了，但又会引起识别不出来，显示一个？号，也不符合要求
2、集 这个字，经过转换后再LENGTH，得到的值是2，而⑥则还是1

[ 本帖最后由 ClearWindxsw 于 2008-12-3 14:30 编辑 ]

ClearWindxsw

sqysl兄，你的是oracle 11g吗，怎么convert转换会报错，我的执行倒是没报错

ClearWindxsw

1. 
   
2. --字符集
   
3. SQL> SELECT USERENV('language') LANG  FROM DUAL;
   
4. 
   
5. LANG
   
6. ----------------------------------------------------
   
7. SIMPLIFIED CHINESE_CHINA.ZHS16GBK
   
8. 
   
9. SQL> --version
   
10. SQL> SELECT VERSION FROM V$INSTANCE;
    
11. 
    
12. VERSION
    
13. -----------------
    
14. 10.2.0.1.0
    
15. 
    
16. SQL> ------默认情况下的特殊字符和'集'的显示及DUMP-----------
    
17. SQL> --默认
    
18. SQL> SELECT 'default' REMARK, '⑥' STR, LENGTH('⑥') L_STR,
    
19.   2         LENGTHB('⑥') LB_STR, DUMP('⑥', 1016) D_STR
    
20.   3    FROM DUAL
    
21.   4  UNION
    
22.   5  SELECT 'default' REMARK, '集' STR, LENGTH('集') L_STR,
    
23.   6         LENGTHB('集') LB_STR, DUMP('集', 1016) D_STR
    
24.   7    FROM DUAL
    
25.   8  UNION --联合一下方便查看
    
26.   9  --convert之后的
    
27. 10  SELECT 'convert' REMARK,
    
28. 11         CONVERT('⑥', 'UTF8', 'zhs16gbk') STR,
    
29. 12         LENGTH(CONVERT('⑥', 'UTF8', 'zhs16gbk')) L_STR,
    
30. 13         LENGTHB(CONVERT('⑥', 'UTF8', 'zhs16gbk')) LB_STR,
    
31. 14         DUMP(CONVERT('⑥', 'UTF8', 'zhs16gbk'), 1016) D_STR
    
32. 15    FROM DUAL
    
33. 16  UNION
    
34. 17  SELECT 'convert' REMARK,
    
35. 18         CONVERT('集', 'UTF8', 'zhs16gbk') STR,
    
36. 19         LENGTH(CONVERT('集', 'UTF8', 'zhs16gbk')) L_STR,
    
37. 20         LENGTHB(CONVERT('集', 'UTF8', 'zhs16gbk')) LB_STR,
    
38. 21         DUMP(CONVERT('集', 'UTF8', 'zhs16gbk'), 1016) D_STR
    
39. 22    FROM DUAL;
    
40. 
    
41. REMARK  STR      L_STR     LB_STR D_STR
    
42. ------- --- ---------- ---------- -------------------------------------------
    
43. convert 鈶?         1          3 Typ=1 Len=3 CharacterSet=ZHS16GBK: e2,91,a5
    
44. convert 闆?         1          3 Typ=1 Len=3 CharacterSet=ZHS16GBK: e9,9b,86
    
45. default ⑥           1          2 Typ=96 Len=2 CharacterSet=ZHS16GBK: a2,de
    
46. default 集           1          2 Typ=96 Len=2 CharacterSet=ZHS16GBK: bc,af
    
47. 
    
48. SQL> -------------对RPAD进行的测试-------------
    
49. SQL> SELECT 'default' REMARK, --默认情况下的RPAD
    
50.   2         RPAD('⑥', 3, '-') STR,
    
51.   3         LENGTH(RPAD('⑥', 3, '-')) L_STR,
    
52.   4         LENGTHB(RPAD('⑥', 3, '-')) LB_STR,
    
53.   5         RPAD('集', 3, '-') ZH,
    
54.   6         LENGTH(RPAD('集', 3, '-')) L_ZH,
    
55.   7         LENGTHB(RPAD('集', 3, '-')) LB_ZH
    
56.   8    FROM DUAL
    
57.   9  UNION
    
58. 10  SELECT 'convert' REMARK, --convert后的RPAD
    
59. 11         RPAD(CONVERT('⑥', 'UTF8', 'zhs16gbk'), 3, '-') STR,
    
60. 12         LENGTH(RPAD(CONVERT('⑥', 'UTF8', 'zhs16gbk'), 3, '-')) L_STR,
    
61. 13         LENGTHB(RPAD(CONVERT('⑥', 'UTF8', 'zhs16gbk'), 3, '-')) LB_STR,
    
62. 14         RPAD(CONVERT('集', 'UTF8', 'zhs16gbk'), 3, '-') ZH,
    
63. 15         LENGTH(RPAD(CONVERT('集', 'UTF8', 'zhs16gbk'), 3, '-')) L_ZH,
    
64. 16         LENGTHB(RPAD(CONVERT('集', 'UTF8', 'zhs16gbk'), 3, '-')) LB_ZH
    
65. 17    FROM DUAL;
    
66. 
    
67. REMARK  STR      L_STR     LB_STR ZH        L_ZH      LB_ZH
    
68. ------- --- ---------- ---------- --- ---------- ----------
    
69. convert 鈶-          2          3 闆-          2          3
    
70. default ⑥-          2          3 集-          2          3
    
71. 
    
72. SQL>
    

复制代码

以上结果是让同事在其他数据库上执行的结果
结果：长度是没问题，但转换全成了乱码

sqysl

我的是11g，其实，我们通过测试都可以看到：
在转换过程中，字符编码及存储长度都正常，只是涉及到RPAD函数的问题，RPAD函数到底是根据什么运算的呢？肯定不是根据编码，因为，我们看到了转换前和转换后的编码都是相同的，不同的只是数据类型，难道是因为数据类型的变化吗？还有，为什么出现乱码吗？是因为我们环境的设置问题，但可以肯定的是显示问题，实际的编码是正确的。有待于研究。

ClearWindxsw

sqysl兄，在SQL Reference 10gr2.pdf中对RPAD的解释原文如下：
The argument n is 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.
oracle自己也知道对多字节的字符集中，显示的最终结果会与实际的n不相符，还以为他们知道后会解决，但没想到在老兄所使用的11g中，非但没解决，还看到convert函数在转换后会报错，至少10g还没报这个错，唉。

要不是他们自己写着会不同，我就发到metalink上去了。
反正我现在对这种需要有定长输出到文本中的，我都使用自己对RPAD封装后的自定义函数去实现，LPAD也是如此。

ClearWindxsw

编码和显示的问题，还要请对字符集编码有研究的同学帮忙分析一下了。最好能详细的解释一下，毕竟现在都在慢慢使用UNICODE了。

sqysl

原帖由 ClearWindxsw 于 2008-12-4 09:22 发表
sqysl兄，在SQL Reference 10gr2.pdf中对RPAD的解释原文如下：
The argument n is 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.
oracle自己也知道对多字节的字符集中，显示的最终结果会与实际的n不相符，还以为他们知道后会解决，但没想到在老兄所使用的11g中，非但没解决，还看到convert函数在转换后会报错，至少10g还没报这个错，唉。

要不是他们自己写着会不同，我就发到metalink上去了。
反正我现在对这种需要有定长输出到文本中的，我都使用自己对RPAD封装后的自定义函数去实现，LPAD也是如此。

其实，我们可以看到，rpad中的n，是指的占位符数，length函数返回的是字符个数，它们不是一回事；
但在我们的测试中，RPAD和LENGTH函数在不进行字符集转换时是没问题的，在字符集转换后这些有关字符个数和占位符数的问题上就出现了问题，还有在tuf8中，"⑥"存储占用了三个字节，而在RPAD中，却用了一个占位符，老弟不妨发到metalink上问一下。

strawbingo

楼主，我也碰到和你同样的问题，比你晚了8个月。

我碰的字和你不同，道理一样。特殊汉字“?”，为上面一个龙，下面一个天，飞龙在天的意思。
数据库环境也是UTF－8。
看你之前的测试，在zhs16gbk字符集下，是不存在该问题的。

我的理解：在UTF-8中汉字存储占三个字节，字母占一个字节。而zhs16gbk中汉字存储占两个字节。
RPAD函数在判断“?”或“⑥”实际判断为1，而实际存储时却占了3位。
导致RPAD执行后实际的要长N位，N=特殊字符的个数。

楼主的解决方法时改写rpad函数，我用了另一种方法，供参考一下。
使用函数to_nchar将字符先转换一下就不会有问题了。

SQL> select RPAD(nvl('⑥',nvl(' ',' ')), 10, nvl(' ',' ')),length(RPAD(nvl('⑥',nvl(' ',' ')), 10, nvl(' ',' '))) from dual;

RPAD(NVL('⑥',NVL('','')),10,N  LENGTH(RPAD(NVL('⑥',NVL('',''
------------------------------ ------------------------------
⑥                                                         10
SQL> select RPAD(nvl(to_nchar('⑥'),nvl(' ',' ')), 10, nvl(' ',' ')),length(RPAD(nvl(to_nchar('⑥'),nvl(' ',' ')), 10, nvl(' ',' '))) from dual;

RPAD(NVL(TO_NCHAR('⑥'),NVL(''  LENGTH(RPAD(NVL(TO_NCHAR('⑥')
------------------------------ ------------------------------
⑥                                                          9