Description of Oracle length and lengthb Functions

 

I. Official Website description

 

Http://download.oracle.com/docs/cd/E11882_01/server.112/e26088/functions088.htm#SQLRF00658

 

Purpose

The length functionsreturn the length of char. Length calculates length usingcharacters as defined by the input character set.

-- Returns the length in characters.

Lengthb usesbytes instead of characters.

-- Returns the length in bytes.

Lengthc usesunicode complete characters.

-- Returns the length in units of Unicode full characters.

Leng2usesucs2 code points.

-- Returns the length in the unit of ucs2 code points.

Length4 usesucs4 code points.

-- Returns the length in the unit of ucs4 code points.

 

Char can beany of the Data Types char, varchar2, nchar, nvarchar2, clob, or nclob.

The exceptionsare lengthc, leng2, and length4, which do not allow char tobe a clob or nclob. the return value is of data type number. if Char has data type char, then the length should des all trailingblanks. if char is null, then this function returns NULL.

 

Restriction on lengthb (restrictions of the lengthb function)

The lengthb functionis supported for single-byte lobs only. It cannot be used with clob and nclob dataIn A multibyte character set.

 

Examples

The followingexample uses the length function using a single-byte databasecharacter set:

 

Select length ('canonicalde') "length incharacters" from dual;

 

Length in characters

--------------------

7

 

The next example assumes a double-bytedatabase character set.

 

Select lengthb ('canonicalde') "length inbytes" from dual;

 

Length in bytes

---------------

14

 

Ii. Example

In different databases, lengthb may obtain different values due to different character sets. For example, zhs16gbk uses two bytes to define a Chinese character. In utf8, three bytes are used.

 

Sys @ anqing1 (Rac1)> select userenv ('language') from dual;

 

Userenv ('language ')

----------------------------------------------------

American_america.zhs16gbk

 

 

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

2

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

4

 

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

6

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

6

 

Through this example, we can see that an important use of the length and lengthb functions is to determine whether the record value contains Chinese content.

 

If there are Chinese characters, then length ()! = Lengthb ()

If there is no Chinese, then length () = lengthb ()

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 (full) dba5 group: 142216823 (full)

Dba6 group: 158654907 (full) dba7 group: 69087192 (full) dba8 group: 172855474

DBA super group 2: 151508914 dba9 group: 102954821 chat group: 40132017 (full)

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.