Oracle LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function with ExamplesIn this article, we’ll look at the variations of the Oracle LENGTH function – LENGTH2, LENGTH4, LENGTHB, and LENGTHC.

Purpose of the Oracle LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function

The purpose of these LENGTH function variants is the same as the basic LENGTH function – to find the length of a specified string.

However, the difference with these functions is that they are useful when working with special character sets.

The differences are:

  • LENGTH: Returns the length of the string
  • LENGTH2: Returns the length of the string in UCS2 code points
  • LENGTH4: Returns the length of the string in UCS4 code points
  • LENGTHB: Returns the length of the string in bytes
  • LENGTHC: Returns the length of the string in characters

 

Syntax

The syntax of the LENGTH2, LENGTH4, LENGTHB, and LENGTHC functions are all the same:

LENGTH2 ( string )

LENGTH4 ( string )

LENGTHB ( string )

LENGTHC ( string )

 

Parameters

The parameters of these LENGTH variation functions are:

  • string (mandatory): The string value to check the length of.

The string value can be any data type out of CHAR, NCHAR, VARCHAR2, or NVARCHAR2. It cannot be a CLOB or NCLOB.

If the string is null, then the function will return NULL.

 

Examples of the LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function

This example shows how each of the LENGTH functions treat a specific string.

SELECT
'Database Star table column row' AS sample,
LENGTHB('Database Star table column row') AS lengthb_test,
LENGTHC('Database Star table column row') AS lengthc_test,
LENGTH2('Database Star table column row') AS length2_test,
LENGTH4('Database Star table column row') AS length4_test
FROM dual;

Result:

SAMPLE LENGTHB_TEST LENGTHC_TEST LENGTH2_TEST LENGTH4_TEST
Database Star table column row 30 30 30 30

 

Similar Functions

Some functions which are similar to these functions are:

  • LENGTH: The basic LENGTH function which finds the length of a string.
  • INSTR2/INSTR4/INSTRB/INSTRC: The variations of the INSTR function which find a smaller string inside a larger string.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Improve Your Oracle SQL With My 10-Day Email Course

x