SUBSTRING extraction in SQL - Mini Project: Build & Apply

Extracting Substrings from Product Codes

📖 Scenario: You work in a retail company database. Each product has a code that contains a category prefix followed by a numeric ID. You want to extract the category prefix from each product code.

🎯 Goal: Build a SQL query that extracts the first three characters (category prefix) from each product code in the products table.

📋 What You'll Learn

Create a table called products with columns product_id (integer) and product_code (string).

Insert exactly these rows into products: (1, 'ABC12345'), (2, 'XYZ98765'), (3, 'DEF45678').

Write a SQL query that selects product_id and the first three characters of product_code as category.

Use the SUBSTRING function to extract the category prefix.

💡 Why This Matters

🌍 Real World

Extracting parts of strings like product codes is common in databases to categorize or filter data.

💼 Career

Database developers and analysts often use substring extraction to clean and analyze data stored in text fields.

1

Create the products table and insert data

Create a table called products with columns product_id as integer and product_code as varchar(20). Insert these exact rows: (1, 'ABC12345'), (2, 'XYZ98765'), and (3, 'DEF45678').

SQL

-- Create the products table and insert the rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a SELECT statement to retrieve product data

Write a SQL SELECT statement to get product_id and product_code from the products table.

SQL

CREATE TABLE products (
  product_id INT,
  product_code VARCHAR(20)
);

INSERT INTO products (product_id, product_code) VALUES
(1, 'ABC12345'),
(2, 'XYZ98765'),
(3, 'DEF45678');

-- Write a SELECT statement to get product_id and product_code
-- Your code here

Need a hint?

Use SELECT product_id, product_code FROM products; to retrieve the data.

3

Extract the category prefix using SUBSTRING

Modify the SELECT statement to extract the first three characters of product_code using SUBSTRING(product_code, 1, 3) and name this column category.

SQL

CREATE TABLE products (
  product_id INT,
  product_code VARCHAR(20)
);

INSERT INTO products (product_id, product_code) VALUES
(1, 'ABC12345'),
(2, 'XYZ98765'),
(3, 'DEF45678');

-- Extract the first three characters of product_code as category
SELECT product_id, SUBSTRING(product_code, 1, 3) AS category FROM products;

Need a hint?

Use SUBSTRING(product_code, 1, 3) to get the first three characters.

4

Complete the query with ordering

Add an ORDER BY product_id clause at the end of the SELECT statement to sort the results by product_id.

SQL

CREATE TABLE products (
  product_id INT,
  product_code VARCHAR(20)
);

INSERT INTO products (product_id, product_code) VALUES
(1, 'ABC12345'),
(2, 'XYZ98765'),
(3, 'DEF45678');

-- Add ORDER BY product_id to sort the results
SELECT product_id, SUBSTRING(product_code, 1, 3) AS category FROM products ORDER BY product_id;

Need a hint?

Use ORDER BY product_id to sort the output by product ID.