Oracle LENGTH

The Oracle LENGTH() function returns the number of characters of a specified string. It measures the length of the string in characters as defined by the input character set.

Syntax

The following illustrates the syntax of the Oracle LENGTH() function:

LENGTH(string_expression);
Code language: SQL (Structured Query Language) (sql)

Arguments

The Oracle LENGTH() function accepts one argument:

string_expression

is the string or an expression that returns a string to be evaluated. The string_expression can be a constant, a variable or a column of a table.

The data type of string_expression argument can be any of the following data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Return value

The LENGTH() function returns a positive integer that represents the number of characters in the string_expression.

If the string_expression is NULL, the LENGTH() function returns NULL.

If the string_expression is CHAR data type, its length will include all leading and trailing blanks.

Examples

The following statement uses the LENGTH() function to calculate the number of characters of the string 'Oracle LENGTH' using a single-byte database character set:

SELECT 'Oracle LENGTH' string, LENGTH('Oracle LENGTH') Len FROM dual;
Code language: SQL (Structured Query Language) (sql)
Oracle LENGTH example

See the following employees table in the sample database:

employees table

The following statement sorts the employees by the lengths of their first names. It uses the LENGTH() function in the ORDER BY clause:

SELECT first_name, LENGTH(first_name) FROM employees ORDER BY LENGTH(first_name) DESC;
Code language: SQL (Structured Query Language) (sql)
Oracle LENGTH with ORDER BY example

The following statement groups employees by the lengths of their first names. It uses the LENGTH() function in the GROUP BY clause:

SELECT LENGTH( first_name ) len, COUNT( * ) FROM employees GROUP BY LENGTH( first_name ) ORDER BY len;
Code language: SQL (Structured Query Language) (sql)
Oracle LENGTH with GROUP BY example

See the following products table:

products table

Suppose, you have to display a list of products with their excerpts on the company’s website.

The following statement uses the LENGTH() function with CONCAT() and SUBSTR() functions to return the excerpts for products.

SELECT product_name, CASE WHEN LENGTH( description ) > 50 THEN CONCAT( SUBSTR( description, 1, 50 ), '...' ) ELSE description END product_excerpt FROM products ORDER BY product_name;
Code language: SQL (Structured Query Language) (sql)

If the product description has the length less than 50, it will be used as the excerpt. Otherwise, the first 50 characters of the description will be used.

In this tutorial, you have learned how to use the Oracle LENGTH() function to get the number of characters of a string.

Was this tutorial helpful?