length()与lengthb()的区别

Oracle Length 函数详解

最新推荐文章于 2021-04-03 09:56:10 发布

原创最新推荐文章于 2021-04-03 09:56:10 发布 · 9.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#character #sql #function #database #oracle #input

oracle 专栏收录该内容

47 篇文章

订阅专栏

本文详细介绍了Oracle数据库中用于计算字符串长度的LENGTH函数及其变种LENGTHB、LENGTHC等的功能和使用方法，并通过实例展示了不同字符集下LENGTH与LENGTHB的区别。

环境：oracle 11.1.0.6.0

区别：length计算字符长度，lengthb计算字节长度。

SQL> select length('a') from dual;

LENGTH('A')
-----------
          1

SQL> select lengthb('a') from dual;

LENGTHB('A')
------------
           1

SQL> select length('A') from dual;

LENGTH('A')
-----------
          1

SQL> select lengthb('A') from dual;

LENGTHB('A')
------------
           1

SQL> select length('你好') from dual;

LENGTH('你好')
--------------
             2

SQL> select lengthb('你好') from dual;

LENGTHB('你好')
---------------
              4

LENGTH

Purpose

The LENGTH functions return the length of char. LENGTH calculates length using characters as defined by the input character set. LENGTHB uses bytes instead of characters. LENGTHC uses Unicode complete characters. LENGTH2 uses UCS2 code points. LENGTH4 uses UCS4 code points.

char can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is of datatype NUMBER. If char has datatype CHAR, then the length includes all trailing blanks. If char is null, then this function returns null.

Restriction on LENGTHB The LENGTHB function is supported for single-byte LOBs only. It cannot be used with CLOB and NCLOB data in a multibyte character set.

Examples

The following example uses the LENGTH function using a single-byte database character set:

SELECT LENGTH('CANDIDE') "Length in characters"
   FROM DUAL;

Length in characters
--------------------
                   7

The next example assumes a double-byte database character set.

SELECT LENGTHB ('CANDIDE') "Length in bytes"
   FROM DUAL;
 
Length in bytes
---------------
             14