Oracle LENGTH Function: A Simple Guide

The LENGTH function in Oracle SQL is one of the most fundamental string functions. It simply tells you the number of characters in a given string.

It's an essential function used for data validation, cleaning, and analysis to understand the size of your text data.

What is the LENGTH Function in Oracle?

The LENGTH function counts the characters in a string and returns that count as a number.

• This count includes all characters: letters, numbers, spaces, and symbols.
• If the string is empty or NULL, LENGTH will return NULL.
• If the data type is CHAR, it will also count any trailing blank spaces used for padding. For VARCHAR2, it only counts the actual characters stored.

This is useful for:

• Data Validation: Enforcing rules like "passwords must be at least 8 characters long."
• Data Cleaning: Finding rows that have unexpectedly short or long values.
• Analysis: Calculating the average length of comments or descriptions.

LENGTH Function Syntax

The syntax for LENGTH is very simple:

LENGTH(char)

Let's break that down:

• char: The string, column, or expression you want to measure (e.g., 'Hello' or last_name).

Oracle LENGTH Function Examples

Here are two practical examples of how to use LENGTH.

Example 1: Finding the Length of a Simple String

This example shows the most basic use of LENGTH on a string that contains letters, a space, and a symbol.

Query:

-- 'Hello, World!' has 13 characters (including the space and !)
SELECT 
  LENGTH('Hello, World!') AS "String Length"
FROM DUAL;

Result:

String Length
-------------
           13

Example 2: Using LENGTH on a Table Column

This is a common use case. Let's find the length of each employee's last name from the employees table.

Query:

SELECT 
  last_name, 
  LENGTH(last_name) AS "Name Length"
FROM employees
WHERE ROWNUM <= 5; -- Just show the first 5 for this example

Result: (The exact results will vary, but will look like this)

LAST_NAME                 Name Length
------------------------- -----------
King                                4
Kochhar                             7
De Haan                             7
Hunold                              6
Ernst                               5