Description of the Oracle Length and LENGTHB functions. (Used to determine if there is any Chinese content in the record value)

I. Description of the official website

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 Unicode full characters.

LENGTH2 usesUCS2 code points.

--Returns the length in UCS2 code point units.

LENGTH4 usesUCS4 code points.

--Returns the length in UCS4 code point units.

Char can beany of the data types char, VARCHAR2, nchar, NVARCHAR2, Clob,or Nclob.

The Exceptionsare lengthc, LENGTH2, and LENGTH4, which do not allow char tobe a CLOB or NCLOB. The return value is of data type number. If Char has a data type char, then the length of includes all trailingblanks. If char is null and then this function returns NULL.

Restriction on LENGTHB (limitations of the LENGTHB function)

The LENGTHB Functionis supported for Single-byte lobs only. It cannot is 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 (' Candide ') "Length incharacters" from DUAL;

Length in characters

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

7

The next example assumes a double-bytedatabase character set.

Select LENGTHB (' Candide ') "Length inbytes" from DUAL;

Length in bytes

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

14

Two. Example Description

In different databases, because of the different character sets, the value that LENGTHB gets may not be the same. If ZHS16GBK uses two byte bits to define a Chinese character. In UTF8, 3 bytes are used.

[Email protected] (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

With this example, we can see that the Length and LENGTHB function is an important use to determine whether there is a Chinese content in the record value.

If there is Chinese, then length ()! = LENGTHB ()

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

Description of the Oracle Length and LENGTHB functions. (Used to determine if there is any Chinese content in the record value)