Oracle PL/SQL Programming, Third Edition

Name

LENGTH, LENGTHB, LENGTHC, LENGTH2, and LENGTH4

Synopsis

The LENGTH family of functions returns the length of a string. The length can be returned in any of the following units:

LENGTH

Characters

LENGTHB

Bytes

LENGTHC

Unicode characters, normalizing where possible

LENGTH2

Code units

LENGTH4

Code points

The same pattern is used for the specification of all LENGTH functions:

FUNCTION LENGTH (string1 VARCHAR2) RETURN NUMBER

If string1 is NULL, then LENGTH returns NULL—not zero! Remember that a NULL string is a “nonvalue.” Therefore, it cannot have a length, even a zero length.

The LENGTH function, in fact, will never return zero; it will always return either NULL or a positive number.

Tip

An exception is when LENGTH is used against a CLOB. It is possible for a CLOB to hold zero bytes and yet not be NULL. In that one case, LENGTH will return zero.

Here are some examples of LENGTH:

LENGTH (NULL) --> NULL
LENGTH ('') --> NULL -- Same as a NULL string.
LENGTH ('abcd') --> 4
LENGTH ('abcd ') --> 5

If string1 is a fixed-length CHAR datatype, then LENGTH counts the trailing blanks in its calculation. So the LENGTH of a fixed-length string is always the declared length of the string. If you want to compute the length of the nonblank characters in string1, you will need to use the RTRIM function to remove the trailing blanks (RTRIM is discussed later in this chapter). In the following example, length1 is set to 60 and length2 is set to 14.

DECLARE company_name CHAR(60) := 'ACME PLUMBING'; length1 NUMBER; ...