In Oracle, the LENGTH() function returns the length of its argument.

LENGTH() can also be thought of as a group of functions. There are five separate functions; LENGTH(), LENGTHB(), LENGTHC(), LENGTH2(), and LENGTH4(). Each function calculates the length in a different way.

Syntax

The syntax goes like this:

{ LENGTH
| LENGTHB
| LENGTHC
| LENGTH2
| LENGTH4
}
(char)

The functions calculate lengths as follows:

Function	Calculates length using…
LENGTH()	Characters as defined by the input character set, with the first character of string having position 1.
LENGTHB()	Bytes
LENGTHC()	Unicode complete characters
LENGTH2()	UCS2 code points
LENGTH4()	UCS4 code points

The argument type can be as follows:

• When using the LENGTH() and LENGTHB() syntax, the argument can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• When using the LENGTHC(), LENGTH2() and LENGTH4() syntax, the argument can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2 (but not CLOB or NCLOB).

Example

Here’s a basic example:

SELECT LENGTH('Big fat cat')
FROM DUAL;

Result:

11

Compared with LENGTHB()

This example shows how the results can differ, depending on which specific function you’re using, and the character set involved.

In this case, we compare LENGTH() with LENGTHB():

SELECT 
    LENGTH('Böyük yağlı pişik') AS LENGTH,
    LENGTHB('Böyük yağlı pişik') AS LENGTHB
FROM DUAL;

Result:

   LENGTH    LENGTHB 
_________ __________ 
       17         22

We can see that the two functions returned two different results. This is because some characters in this string use two bytes.

The LENGTH() function returns the length in characters as defined by the input character set, whereas the LENGTHB() function returns the length in bytes.

If we return to the original string, the results are the same between the two functions:

SELECT 
    LENGTH('Big fat cat') AS LENGTH,
    LENGTHB('Big fat cat') AS LENGTHB
FROM DUAL;

Result:

   LENGTH    LENGTHB 
_________ __________ 
       11         11 

This string uses just one byte per character, and so the length in bytes is the same as the number of characters.

Null Argument

If the argument is null, the result is null:

SET NULL 'null';
SELECT 
    LENGTH(null) AS LENGTH,
    LENGTHB(null) AS LENGTHB,
    LENGTHC(null) AS LENGTHC,
    LENGTH2(null) AS LENGTH2,
    LENGTH4(null) AS LENGTH4
FROM DUAL;

Result:

   LENGTH    LENGTHB    LENGTHC    LENGTH2    LENGTH4 
_________ __________ __________ __________ __________ 
     null       null       null       null       null 

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling the function without passing any arguments results in an error:

SELECT LENGTH()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LENGTH()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And passing too many arguments also results in an error:

SELECT LENGTH('Big fat cat', 'oops')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LENGTH('Big fat cat', 'oops')
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: