SQL LENGTH() Function

Summary: in this tutorial, you will learn how to use the SQL LENGTH function to get the number of characters in a string.

Introduction to the SQL LENGTH function #

The SQL LENGTH function returns the number of characters in a string. The LENGTH function is available in every relational database system. Some database systems use a LEN function that has the same effect as the LENGTH function.

Here’s the syntax of the LENGTH function:

LENGTH(string)Code language: SQL (Structured Query Language) (sql)

If the input string is empty, the LENGTH returns 0. If it is NULL, the function returns NULL.

The number of characters is the same as the number of bytes for ASCII strings. For other character sets, they may be different.

The LENGTH function returns the number of bytes in some relational database systems such as MySQL and PostgreSQL. To get the number of characters in a string in MySQL and PostgreSQL, you use the CHAR_LENGTH function instead.

Basic SQL LENGTH Function example #

The following statement uses the LENGTH function to return the number of characters in the string SQL:

SELECT
  LENGTH('SQL') string_length;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 string_length
---------------
             3Code language: SQL (Structured Query Language) (sql)

Using the LENGTH function with table data #

We’ll use the following employees table from the sample database:

The following query uses the LENGTH function with CONCAT function to return the top five employees with the longest names:

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  LENGTH(CONCAT(first_name, ' ', last_name)) AS name_length
FROM
  employees
ORDER BY
  name_length DESC
FETCH FIRST
  5 ROWS ONLY;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

     full_name     | name_length
-------------------+-------------
 Jose Manuel Urman |          17
 Irene Mikkilineni |          17
 Michael Hartstein |          17
 Alexander Hunold  |          16
 Karen Colmenares  |          16

How the query works.

First, the CONCAT function creates employees’ full names by concatenating the first, a space, and last names.
Second, apply the LENGTH function to full names.
Third, sort the result set by the result of the LENGTH function and retrieve the top five rows to get the longest names.

Summary #

Use the SQL LENGTH function to get the number of characters in a 0string.

Databases #

Was this tutorial helpful ?