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>