1.含义及区别
length指的是字符长度,lengthb指的是字节长度。
在不同的数据库,因为字符集的不同,LENGTHB得到的值可能会不一样。如ZHS16GBK采用两个byte位来定义一个汉字。而在UTF8,采用3个byte。
另外,
如果有中文,那么Length() != Lengthb()
如果没有中文,那么Length() == Lengthb()
2.测试
sys@ORCL>select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
sys@ORCL > select length('shall zhong'),lengthb('shall zhong') from dual;
LENGTH('SHALLZHONG') LENGTHB('SHALLZHONG')
-------------------- ---------------------
11 11
sys@ORCL > select length('数'),lengthb('数') from dual;
LENGTH('数') LENGTHB('数')
------------- --------------
1 2
sys@ORCL > select length('数据库 11g'),lengthb('数据库 11g') from dual;
LENGTH('数据库11G') LENGTHB('数据库11G')
---------------------- -----------------------
7 10
sys@ORCL>select sysdate,length(sysdate),lengthb(sysdate) from dual;
SYSDATE LENGTH(SYSDATE) LENGTHB(SYSDATE)
------------------- --------------- ----------------
2016-04-23 14:28:20 19 19
----上面length(sysdate)其实有个隐含的转换函数,length(to_char(sysdate)),lengthb也是
Restriction on LENGTHB (Lengthb函数的限制)
The LENGTHB functionis supported for single-byte LOBs only. It cannot be used with CLOB and NCLOB datain a multibyte character set.
reference
http://docs.oracle.com/cd/E11882_01/olap.112/e23381/row_functions033.htm#OLAXS425
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions088.htm#SQLRF00658
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30130773/viewspace-2107136/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2107136/