substr,substrb,cast的使用、比较

SQL> create table test (name varchar2(40));
 
Table created
 
SQL> insert into test values ('中国.四川.成都');
 
1 row inserted
 
SQL> commit ;
 
Commit complete

SQL> create table test2 (name varchar2(6));
 
Table created
 
SQL> select substr(name,1,6) from test;        //substr按字符进行截取
 
SUBSTR(NAME,1,6)
----------------
中国.四川.
 
SQL> insert into test2 select substr(name,1,6) from test;  //varchar2按照字节进行存储,一个汉字2个字节
 
insert into test2 select substr(name,1,6) from test
 
ORA-12899: value too large for column "DATACENTER"."TEST2"."NAME" (actual: 10, maximum: 6)
 
SQL> insert into test2 select cast(name as varchar2(6)) from test; //case不起作用，不会进行截取
 
insert into test2 select cast(name as varchar2(6)) from test
 
ORA-12899: value too large for column "DATACENTER"."TEST2"."NAME" (actual: 14, maximum: 6)
 
SQL> insert into test2 select substrb(name,1,6) from test;  //substrb按照字节进行截取
 
1 row inserted
 
SQL> commit ;
 
Commit complete
 
SQL> select * from test2;
 
NAME
------
中国.                              //显示的只有3个字符，5个字节数据
 
SQL> select length(name) from test2;
 
LENGTH(NAME)
------------
           4                          //字符数为4
 
SQL> select lengthb(name) from test2;
 
LENGTHB(NAME)
-------------
            6                         //字节数为6
 
SQL> select lengthb(rtrim(name)) from test2;
 
LENGTHB(RTRIM(NAME))
--------------------
                   5                  //包含一个空格，如果截取的长度与汉字的长度不符合(例如2个汉字截取3个字符，那么oracle只会截取一个汉字第三个字符是空格代替，不会出现乱码的情况)
 
SQL> declare                                               //在pl/sql中，用case(xxx as varchar2(6))也可以达到截取字节的效果。
  2  v1 varchar2(40);
  3  begin
  4  select name into v1 from test;
  5  insert into test2 values (cast(v1 as varchar2(6)));
  6  end;
  7  /
 
PL/SQL procedure successfully completed
 
SQL> commit;
 
Commit complete
 
SQL> select * from test2;
 
NAME
------
中国.
中国.
 
SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-664912/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-664912/