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; ...
Get Oracle PL/SQL Programming, Third Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.