PostgreSQL POSITION Function

Summary: in this tutorial, you’ll learn to use the PostgreSQL POSITION function to find the location of the first occurrence of a substring within a string.

Introduction to the PostgreSQL POSITION function #

The POSITION function allows you to find the location of the first occurrence of a substring within a string.

Here’s the syntax of the POSITION function:

POSITION(substring IN string)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The POSITION function takes two parameters:

• substring: The substring you want to search for.
• string: The source string in which you want to search.

The POSITION function returns an integer representing the starting position (1-indexed) of the first occurrence of the substring in the string.

If the substring does not exist in the string, the POSITION function returns 0. If the substring or string is NULL, the POSITION function returns NULL.

Basic PostgreSQL POSITION function example #

The following example uses the POSITION function to return the location of the first instance of the substring "t" in the string "pgtutorial.com":

SELECT
  POSITION('t' IN 'pgtutorial.com') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

The following example returns zero because the substring "JS" does not exist in the string "PostgreSQL":

SELECT
  POSITION('JS' IN 'PostgreSQL') result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

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

The following statement returns NULL because the substring is NULL:

SELECT
  POSITION(NULL IN 'PostgreSQL Tutorial') 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 POSITION function with table data #

We’ll use the users table from the inventory sample database for the demonstration:

The following example uses the POSITION function to find the location of the @ symbol in email addresses:

SELECT
  email,
  POSITION('@' IN email) AS at_sign_position
FROM
  users
ORDER BY
  email;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

            email             | at_sign_position
------------------------------+------------------
 [email protected]   |               12
 [email protected]     |               10
 [email protected] |               14
 [email protected]    |               11
 [email protected]      |                9Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To extract usernames from emails, you can use the POSITION function with the LEFT function:

SELECT
  email,
  LEFT(email, POSITION('@' IN email) -1) AS username
FROM
  users
ORDER BY
  email;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

            email             |   username
------------------------------+---------------
 [email protected]   | alice.jones
 [email protected]     | bob.brown
 [email protected] | charlie.davis
 [email protected]    | jane.smith
 [email protected]      | john.doeCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How these functions work:

• First, the POSITION function returns the location of the @ in the email address.
• Second, the LEFT function extracts the username from position 1 to the position of the @ sign minus 1.

Summary #

• Use the POSITION function to return the location of the first occurrence of a substring within a string.