Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle String Functions / Oracle LENGTH

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);

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;
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;
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;
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;

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?
  • YesNo
Previous Oracle INITCAP
Next Oracle LOWER

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

String Functions

  • ASCII
  • CHR
  • CONCAT
  • CONVERT
  • DUMP
  • INSTR
  • INITCAP
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • REPLACE
  • REGEXP_COUNT
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TRIM
  • UPPER

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.