BBampaa

Lessons › SQL › Deep Dive: Text, Date & Number Functions

SQL lesson

POSITION() and STRPOS() for substring index

POSITION() and STRPOS() both find the location of one piece of text inside another, returning a number that tells you how many characters in it starts - or zero if it isn't there at all.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Finding where a character sits inside text

SELECT address, POSITION(',' IN address) AS comma_pos FROM customers

Result:

addresscomma_pos
Toronto, Canada8
Berlin, Germany7
Mumbai, India7
Lagos, Nigeria6

Step by step

  1. Run the query without POSITION so you can see what the unfiltered values look like.
    SELECT address, IN FROM customers;
  2. POSITION returns the index where a substring first appears (1-based).
    SELECT address, POSITION(',' IN address) FROM customers;
  3. AS gives the POSITION result a friendly column name so the output reads cleanly.
    SELECT address, POSITION(',' IN address) AS comma_pos FROM customers;

Practice

Try each one in your head first, then open the answer.

1. Write a query on the customers table that returns the email column and the position of the first space in each email value, named email_space_pos.
SELECT email, POSITION(' ' IN email) AS email_space_pos FROM customers
2. Write a query on the products table that returns the brand column and the position of the first space in each brand value, named brand_space_pos.
SELECT brand, POSITION(' ' IN brand) AS brand_space_pos FROM products
3. Write a query on the suppliers table that returns the country column and the position of the first space in each country value, named country_space_pos.
SELECT country, POSITION(' ' IN country) AS country_space_pos FROM suppliers
4. Write a query on the employees table that returns the full_name column and the position of the first space in each full_name value, named full_name_space_pos.
SELECT full_name, POSITION(' ' IN full_name) AS full_name_space_pos FROM employees
5. Write a query on the reviews table that returns the customer_id column and the position of the first space in each customer_id value, named customer_id_space_pos.
SELECT customer_id, POSITION(' ' IN customer_id) AS customer_id_space_pos FROM reviews

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed