BBampaa

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

SQL lesson

SUBSTRING() / LEFT() / RIGHT()

These functions slice out a specific portion of a text value - the beginning, the end, or any section in the middle.

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

Example

Extracting Retail Codes with SUBSTRING(), LEFT(), and RIGHT()

SELECT product_id,
       LEFT(product_id, 4) AS category_code,
       RIGHT(product_id, 5) AS item_number,
       SUBSTRING(product_id, 6, 5) AS mid_section
FROM products

Result:

product_idcategory_codeitem_numbermid_section
ELEC-00142ELEC0014200142
CLTH-00389CLTH0038900389
HOME-00217HOME0021700217
BEAU-00056BEAU0005600056
SPRT-00901SPRT0090100901

Step by step

  1. Run the query without SUBSTRING so you can see what the unfiltered values look like.
    SELECT product_id,
       LEFT(product_id, 4) AS category_code,
       RIGHT(product_id, 5) AS item_number,
       product_id
FROM products;
  2. SUBSTRING pulls characters from text by position and length.
    SELECT product_id,
       LEFT(product_id, 4) AS category_code,
       RIGHT(product_id, 5) AS item_number,
       SUBSTRING(product_id, 6, 5)
FROM products;
  3. AS gives the SUBSTRING result a friendly column name so the output reads cleanly.
    SELECT product_id,
       LEFT(product_id, 4) AS category_code,
       RIGHT(product_id, 5) AS item_number,
       SUBSTRING(product_id, 6, 5) AS mid_section
FROM products;

Practice

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

1. Using customers, write a query to show the first 2 characters of email.
SELECT LEFT(email, 2) FROM customers
2. Using products, write a query to show the last 3 characters of product_id.
SELECT RIGHT(product_id, 3) FROM products
3. Using suppliers, write a query to show the first 3 characters of supplier_id starting at position 1.
SELECT SUBSTRING(supplier_id, 1, 3) FROM suppliers
4. Using employees, write a query to show the first 3 characters of employee_id.
SELECT LEFT(employee_id, 3) FROM employees
5. Using reviews, write a query to show the last 2 characters of customer_id.
SELECT RIGHT(customer_id, 2) 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