Oracle LENGTH

LENGTH is an Oracle function that returns the length of string, i.e, total number of characters in a string.

The syntax of LENGTH function is as follows:

LENGTH ( expression);

Here,

expression = It could be a string or column name. The data type of this expression must be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. If the expression is NULL, then function also returns NULL.

This function returns a positive number representing the number of characters in a string and includes all the characters, special characters and even blank spaces.

Oracle Length Function Examples

Lets see one simple query which returns the total number of characters in a string.

SELECT 'Learning is fun.' String,
LENGTH('Learning is fun.') Length
from 
dual;

Result:

The first part of the query returns the string itself, while the LENGTH function returns the number of characters in the string.

We will be using Employees Table from HR schema for the coming queries:

We will use this query to find the total number of characters in first_name and last_name column of Employees table:

SELECT
first_name,
LENGTH(first_name),
last_name,
LENGTH(last_name)FROM employees;

Result:

The query returns the length of names(string) in first_name and last_name columns.

Let’s use concatenation operator ( || ) to combine the first_name and last_name column and find the length of Full_name using LENGTH function.

SELECT
first_name || ' ' || last_name AS Full_name,
LENGTH(first_name || ' ' || last_name) Length
FROM
employees;

Result:

The query concatenates the first name and last name of employees and returns the length of full name using LENGTH function.

The following query is used to find the length of full name of employees and we group them with length so that we can how many other employees have full names with exact same length.

In this we concatenate the first name and last name of employees and use the COUNT () function to count the length of Full name. Finally, we group them by Length.

SELECT
LENGTH(first_name || ' ' || last_name) Length,
COUNT(*)
FROM
employees
GROUP BY LENGTH(first_name || ' ' || last_name)
ORDER BY LENGTH(first_name || ' ' || last_name);

Result:

The query returns the length of full name of employees and tells us how many employees exist in the table, whose full name have exact same length.

From the result, we can find that majority of employees’ full name length is between 11 to 15 characters.

In this article, we learned to use LENGTH function and we saw examples which combined LENGTH function with other string functions to perform different kinds of operation on the table.