SQL POSITION Function

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

Introduction to the SQL POSITION function #

In SQL, the POSITION function returns an integer that represents 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: SQL (Structured Query Language) (sql)

The POSITION accepts two parameters:

• substring: The sequence of characters you want to find.
• string: The string where you want to search for the substring.

The POSITION function returns an integer representing the location of the first occurrence of a substring. The position is a 1-base index, meaning that the first character in a string has the position 1, and so on.

If the substring does not exist, the POSITION function returns 0.

Basic SQL POSITION example #

The following example uses the POSITION example to find the first occurrence of the string "SQL" in the string "SQL will, SQL will rock you!":

SELECT
  POSITION('SQL' IN 'SQL will, SQL will rock you!') AS sql_position;Code language: PHP (php)

Output:

 sql_position
--------------
            1

The following example returns zero because the input string doesn’t have the substring “We”:

SELECT
  POSITION('We' IN 'SQL will, SQL will rock you!') AS result;Code language: PHP (php)

Output:

 result
--------
      0

Finding the Position of a Substring in Employee Names #

We’ll use the employees from the HR sample database to demonstrate the POSITION function:

The following query uses the POSITION function to find employees with the letter A in the first_name column along with the position:

SELECT
  first_name,
  POSITION('A' IN first_name) AS position_of_a
FROM
  employees
WHERE
  POSITION('A' IN first_name) > 0;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | position_of_a
------------+---------------
 Alexander  |             1
 Alexander  |             1
 Adam       |             1
...Code language: SQL (Structured Query Language) (sql)

Filtering Rows Based on Substring Position #

The following statement uses the POSITION function to find employees whose email addresses start with the letter J:

SELECT
  email
FROM
  employees
WHERE
  POSITION('j' IN email) = 1;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

               email
-----------------------------------
 [email protected]
 jose [email protected]
 [email protected]
 [email protected]
 [email protected]
 [email protected]
 [email protected]
 [email protected]Code language: SQL (Structured Query Language) (sql)

In this example, we use the POSITION function in the WHERE clause to filter rows where the letter j is the first character in the email column.

Using the POSITION function with CASE expressions #

The following query uses the POSITION function with a CASE expression to query flag employees whose phone numbers start with 555:

SELECT
  phone_number,
  CASE
    WHEN POSITION('515' IN phone_number) > 0 THEN 'Starts with 515'
    ELSE 'Does not start with 515'
  END AS status
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 phone_number  |         status
---------------+-------------------------
 515-123-4567  | Starts with 515
 515-123-4568  | Starts with 515
 515-123-4569  | Starts with 515
 590-423-4567  | Does not start with 515
 590-423-4568  | Does not start with 515
...Code language: SQL (Structured Query Language) (sql)

Summary #

• Use the POSITION function to return the position of the first occurrence of a substring (1-based) in an input string.
• The POSITION function returns zero if the substring is unavailable in the input string.

Databases #

• PostgreSQL POSITION Function