PostgreSQL LEFT Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL LEFT() function to return the first n characters in a string.

Introduction to the PostgreSQL LEFT Function #

In PostgreSQL, the LEFT() function allows you to extract the first n characters from a string.

Here’s the basic syntax of the LEFT function:

LEFT(string, n)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The LEFT() function accepts two parameters:

• string: The input string from which you want to extract characters.
• n: The number of characters you want to extract from the input string. If n is negative, the LEFT() function returns all characters but the last |n| characters.

|n| is the absolute value of n. For example if n is -5, the |n| will be 5.

The LEFT() function returns a string containing first n characters of the input string. It returns NULL if the string or the n is NULL.

Basic PostgreSQL LEFT function examples #

The following query uses the LEFT() function to return the first two characters from the string "pgtutorial.com":

SELECT
  LEFT('pgtutorial.com', 2) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 pgCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the LEFT() function with a negative n (-4) to return all characters of the string "pgtutorial.com" except the last four characters:

SELECT
  LEFT('pgtutorial.com', -4) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   result
------------
 pgtutorialCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the LEFT() function with the NULL input string:

SELECT
  LEFT(NULL, -1) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PostgreSQL LEFT function with table data #

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

The following statement uses the LEFT() function to return the first letters of category names:

SELECT
  category_name,
  LEFT(category_name, 1) AS initial                   
FROM
  categories
ORDER BY
  category_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   category_name    | initial
--------------------+---------
 Accessories        | A
 Audio Systems      | A
 Computers          | C
 Desktops           | D
 Electronics        | E
 Home Entertainment | H
 Laptops            | L
 Mobile Devices     | M
 Smartphones        | S
 Tablets            | T
 Televisions        | T
 Wearables          | WCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the LEFT function to filter data #

The following query uses the LEFT() function in the WHERE clause to find categories with the first character A:

SELECT
  category_name
FROM
  categories
WHERE
  LEFT(category_name, 1) = 'A';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 category_name
---------------
 Accessories
 Audio SystemsCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the LEFT function with an aggregate function #

The following query uses the LEFT() function with the COUNT aggregate function to group categories by their initials:

SELECT
  LEFT(category_name, 1) AS initial,
  COUNT(category_name)
FROM
  categories
GROUP BY
  LEFT(category_name, 1)
ORDER BY
  initial;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 initial | count
---------+-------
 A       |     2
 C       |     1
 D       |     1
 E       |     1
 H       |     1
 L       |     1
 M       |     1
 S       |     1
 T       |     2
 W       |     1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The query returns the first letter of each category and the number of categories for each letter.

Summary #

• Use the LEFT() function to extract the first n character from a string.