The Oracle Length function is a built-in function in the Oracle database to return the specified string’s size. The function takes one parameter, which is the string, this string could be one of many character data types like char, varchar2, nvarchar2, clob, or nclob.

In this tutorial we will explain how to use the Oracle length function in many clauses like:

• Find String Length in Oracle
• Find NULL String Length in Oracle
• Find Column Value Length in Oracle
• Using Oracle Length Function with Case Expression
• Using Oracle Length Function in ORDER BY Clause
• Using Oracle Length Function in GROUP BY Clause

Oracle Length Syntax:

Now, let’s look at the basic syntax of the Oracle LENGTH() function.

LENGTH(string)

Note:

• Oracle LENGTH returns the number data type.
• The Oracle LENGTH function returns a null value if the string is null.

Oracle LENGTH() Function Examples

We will give multiple examples to showcase the use of the oracle length function in different cases and SQL clauses.

Find String Length in Oracle Example:

SELECT LENGTH('oraask.com')
FROM dual;

Output:

Explanation:

In the above example, we have a string “oraask.com“, and we want to find how many characters are there in this string value. So, to achieve this, we used the length function and passed our string value to it. the result of this statement is 10 characters.

Find NULL String Length in Oracle Example:

SELECT LENGTH('') "Length of null value"
FROM dual;

Output:

Explanation:

In the above example, we have a null string “”, and we want to find how many characters are there in this string null value. So, in this case, the length function returns null.

Find Column Value Length in Oracle Example:

SELECT FIRST_NAME, LENGTH(FIRST_NAME) FIRST_NAME_LENGTH 
FROM employees;

Output:

Explanation:

In the above example, we have a “first_name” column with a string value of the first name of each employee, and we want to find how many characters are in each first name. So, in this case, the length function returns the length of the respective name of the employee from the employee’s table.

Using Oracle Length Function with Case Expression Example:

We can use the length function within case statements as a case condition.

SELECT FIRST_NAME
      , (CASE
           WHEN LENGTH (FIRST_NAME) > 5 THEN CONCAT (SUBSTR (FIRST_NAME, 1, 5), '...')
           ELSE FIRST_NAME
         END)
         LONG_FIRST_NAME
      ,LENGTH (FIRST_NAME) FIRST_NAME_LENGTH
FROM   EMPLOYEES;

Output:

Explanation:

In the above example, we used the length function with a case statement to check that the first name has a value of more than 5 characters to consider it a long name and concatenate two dots or periods to the name after cutting the extra characters.

Using Oracle Length Function in ORDER BY Clause Example:

We can use the length function inside an order by clause to sort the result set by ascending or descending based on the column value length.

SELECT   FIRST_NAME, LENGTH (FIRST_NAME) FIRST_NAME_LENGTH
FROM     EMPLOYEES
ORDER BY LENGTH (FIRST_NAME) DESC;

Output:

Explanation:

In the above example, we used the length function inside order by clause to sort the result from the employee’s table in descending order depending on the first name column value length.

Using Oracle Length Function in GROUP BY Clause Example:

We can use the length function inside an group by clause to summarize the result set based on the column value length.

SELECT   LENGTH (FIRST_NAME) FIRST_NAME_LENGTH, COUNT(*)
FROM     EMPLOYEES
GROUP BY LENGTH (FIRST_NAME)
ORDER BY LENGTH (FIRST_NAME) DESC;

Output:

Explanation:

In the above example, we used the length function inside the group by clause to group the result by the first name column value length, and to give you meaningful insights into the result; we added another column in the SQL statement above to get the number of each length value in the first name column which means we have 2 employees the first name value length is 11 characters. We also have 35 employees; the first name value length is 5, and so on.

Conclusion

Oracle Length function is widely used in SQL statements, and we have the option to use it in multiple places to leverage its power. In this article, we have covered 4 different examples to demonstrate where the length function could be used in SQL statements.