Oracle LENGTH2() Function

The Oracle (PL/SQL) LENGTH2() function returns the length of the specified string. It calculates length using UCS2 code points. If the specified string is NULL, then this function returns NULL.

Syntax

LENGTH2(string)

Parameters

string Required. Specify the string to return the length for. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Return Value

Returns the length of the specified string, using UCS2 code points.

Example 1:

The example below shows the usage of LENGTH2() function.

LENGTH2('12345')
Result: 5

LENGTH2('ABCDE')
Result: 5

LENGTH2(12345)
Result: 5

LENGTH2('AlphaCodingSkills')
Result: 17

LENGTH2('Alpha Coding Skills')
Result: 19

LENGTH2(NULL)
Result: NULL

LENGTH2('')
Result: NULL

LENGTH2(' ')
Result: 1

Example 2:

Consider a database table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

The statement given below can be used to get the length of records of City column (using UCS2 code points).

SELECT Employee.*, 
LENGTH2(City) AS LENGTH2_Value 
FROM Employee;

The query will produce the following result:

EmpID	Name	City	Age	LENGTH2_Value
1	John	London	25	6
2	Marry	New York	24	8
3	Jo	Paris	27	5
4	Kim	Amsterdam	30	9
5	Ramesh	New Delhi	28	9
6	Huang	Beijing	28	7

❮ Oracle Functions