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