PostgreSQL LENGTH Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL LENGTH() function to return the number of characters in a string.

Introduction to the PostgreSQL LENGTH function #

The LENGTH() function returns the number of characters of a string.

Here’s the syntax of the LENGTH function:

LENGTH(string)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The LENGTH() function takes one parameter:

• string is the input string you want to count the characters.

The LENGTH() function returns an integer representing the number of characters in the string.

If the input string is NULL, the LENGTH() function returns NULL.

In PostgreSQL, the CHAR_LENGTH and CHARACTER_LENGTH functions are the synonyms for LENGTH function.

Basic PostgreSQL LENGTH function examples #

The following statement uses the LENGTH() function to return the number of characters in the string "pgtutorial.com":

SELECT
  LENGTH('pgtutorial.com') string_length;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 string_length
---------------
            14Code language: plaintext (plaintext)

The following example uses the LENGTH function with the input string as NULL and returns NULL :

SELECT
  LENGTH(NULL) string_length;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 string_length
---------------
          NULLCode language: plaintext (plaintext)

Using the LENGTH function with table data #

We’ll use the products table from the inventory database:

The following statement uses the LENGTH() function to return the number of characters for each product name:

SELECT
  product_name,
  length(product_name) product_name_length
FROM
  products
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | product_name_length
----------------------------+---------------------
 Apple AirPods Pro 3        |                  19
 Apple iMac 24"             |                  14
 Apple iPad Pro 12.9        |                  19
 Apple iPhone 15            |                  15
 Apple iPhone 15 Pro Max    |                  23
...Code language: plaintext (plaintext)

Sorting the products by product name’s length #

The following statement retrieves the product names and sorts them by their length from longest to shortest:

SELECT
  product_name,
  LENGTH(product_name) product_name_length
FROM
  products
ORDER BY
  product_name_length DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | product_name_length
----------------------------+---------------------
 Microsoft Surface Laptop 5 |                  26
 Samsung Galaxy Buds Pro 2  |                  25
 Lenovo ThinkPad X1 Carbon  |                  25
 Apple iPhone 15 Pro Max    |                  23
 Samsung QN900C Neo QLED    |                  23
 Samsung Galaxy Z Fold 5    |                  23
 Samsung Galaxy Watch 6     |                  22
...Code language: plaintext (plaintext)

Filtering strings by their lengths #

The following query uses the LENGTH() function in the WHERE clause to retrieve the products with a name’s length greater than 23:

SELECT
  product_name,
  LENGTH(product_name) product_name_length
FROM
  products
WHERE
  LENGTH(product_name) > 23
ORDER BY
  product_name_length DESC;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | product_name_length
----------------------------+---------------------
 Microsoft Surface Laptop 5 |                  26
 Samsung Galaxy Buds Pro 2  |                  25
 Lenovo ThinkPad X1 Carbon  |                  25Code language: plaintext (plaintext)

Summary #

• Use the LENGTH() function to return the number of characters in a string.