Oracle LENGTH() Function

Oracle LENGTH() is a built-in function that returns the character length of a given string.

There are several functions in Oracle used to calculate the length of a string, but they differ in the way they calculate the length:

LENGTH calculates the length of the string based on the characters defined in the input character set.
LENGTHB calculates the length of the string based on bytes instead of characters.
LENGTHC calculates the length of the string based on full Unicode characters.
LENGTH2 calculates the length of the string based on UCS2 code points.
LENGTH4 calculates the length of the string based on UCS4 code points.

Oracle `LENGTH()` Syntax

Here is the syntax for the Oracle LENGTH() function:

LENGTH(str)

str: Required. The string to calculate the length for. It can be of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

The Oracle LENGTH() function returns an integer that is the length of the given string, in number of characters.

If any of the parameters is NULL, LENGTH() returns NULL.

Here are some examples that demonstrate the usage of the Oracle LENGTH() function.

The following statement returns the length of the string ‘Hello’:

SELECT
    LENGTH('Hello')
FROM dual;

Output:

   LENGTH('HELLO')
__________________
                 5

You can use the function to calculate the length of multi-byte strings:

SELECT
    LENGTH('你好')
FROM dual;

Output:

   LENGTH('你好')
_______________
              2

If you need to calculate the byte length of a string, use the LENGTHB() function.

SELECT
    LENGTHB('你好')
FROM dual;

Output:

   LENGTHB('你好')
________________
               6

If any of the parameters is NULL, LENGTH() returns NULL.

SET NULL 'NULL';
SELECT
    LENGTH(NULL)
FROM dual;

Output:

   LENGTH(NULL)
_______________
           NULL

In this example, we use the statement SET NULL 'NULL'; to display the NULL value as the string 'NULL'.

Oracle LENGTH() is a built-in function that returns the character length of a given string.