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

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

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;

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;

Hint

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

Practice

1. What does the SQL function SUBSTRING('Hello World', 7, 5) return?

easy

A. 'Hello W'

B. 'Hello'

C. 'World!'

D. 'World'

Solution

1. Step 1: Understand SUBSTRING parameters

   The function takes the string 'Hello World', starts at position 7, and extracts 5 characters.

2. Step 2: Extract substring from position 7

   Counting characters: H(1) e(2) l(3) l(4) o(5) space(6) W(7) o(8) r(9) l(10) d(11). Starting at 7, 5 characters are 'World'.

3. Final Answer:

   'World' -> Option D

4. Quick Check:

   SUBSTRING('Hello World',7,5) = 'World' [OK]

Hint: Count characters starting at 1, then extract length [OK]

Common Mistakes:

• Starting count at 0 instead of 1
• Extracting fewer or more characters than length
• Including spaces incorrectly

2. Which of the following is the correct syntax to extract 3 characters starting from position 2 in SQL?

easy

A. SUBSTRING('Example', 3)

B. SUBSTRING('Example', 3, 2)

C. SUBSTRING('Example', 2, 3)

D. SUBSTRING('Example', 2)

Solution

1. Step 1: Recall SUBSTRING syntax

   SUBSTRING(string, start_position, length) extracts length characters from start_position.

2. Step 2: Match parameters to requirement

   We want 3 characters starting at position 2, so SUBSTRING('Example', 2, 3) is correct.

3. Final Answer:

   SUBSTRING('Example', 2, 3) -> Option C

4. Quick Check:

   Syntax = SUBSTRING(string, start, length) [OK]

Hint: Syntax is SUBSTRING(string, start, length) [OK]

Common Mistakes:

• Swapping start and length parameters
• Omitting length parameter
• Using zero-based start position

3. Given the table Users with a column username containing 'john_doe123', what is the result of SELECT SUBSTRING(username, 6, 4) FROM Users;?

medium

A. 'doe1'

B. 'doe_'

C. '_doe'

D. 'doe12'

Solution

1. Step 1: Identify substring start and length

   Start at position 6 and extract 4 characters from 'john_doe123'.

2. Step 2: Count characters and extract

   Characters: j(1) o(2) h(3) n(4) _(5) d(6) o(7) e(8) 1(9) 2(10) 3(11). Starting at 6: d(6), o(7), e(8), 1(9) -> 'doe1'.

3. Final Answer:

   'doe1' -> Option A

4. Quick Check:

   SUBSTRING('john_doe123',6,4) = 'doe1' [OK]

Hint: Count characters carefully starting at 1 [OK]

Common Mistakes:

• Starting count at 0
• Extracting wrong number of characters
• Confusing underscore with letter

4. Identify the error in the SQL query: SELECT SUBSTRING('Database', 0, 4);

medium

A. Start position cannot be zero

B. Length cannot be less than 5

C. String must be in double quotes

D. SUBSTRING requires only two parameters

Solution

1. Step 1: Check SUBSTRING start position rules

   In SQL, string positions start at 1, not 0.

2. Step 2: Analyze given query

   Start position is 0, which is invalid and causes an error.

3. Final Answer:

   Start position cannot be zero -> Option A

4. Quick Check:

   Positions start at 1, zero is invalid [OK]

Hint: Start counting at 1, never zero [OK]

Common Mistakes:

• Using zero as start position
• Confusing string quotes
• Wrong number of parameters

5. You have a column product_code with values like 'AB12345XYZ'. Write a SQL query to extract the numeric part (5 digits) starting from position 3. Which query correctly extracts '12345'?

hard

A. SELECT SUBSTRING(product_code, 4, 5) FROM products;

B. SELECT SUBSTRING(product_code, 3, 5) FROM products;

C. SELECT SUBSTRING(product_code, 3, 4) FROM products;

D. SELECT SUBSTRING(product_code, 2, 5) FROM products;

Solution

1. Step 1: Understand the string structure

   'AB12345XYZ' has 'A'(1), 'B'(2), '1'(3), '2'(4), '3'(5), '4'(6), '5'(7), 'X'(8), 'Y'(9), 'Z'(10).

2. Step 2: Extract 5 digits starting at position 3

   Starting at 3, extract 5 characters: '1'(3), '2'(4), '3'(5), '4'(6), '5'(7) -> '12345'.

3. Final Answer:

   SELECT SUBSTRING(product_code, 3, 5) FROM products; -> Option B

4. Quick Check:

   Start=3, length=5 extracts '12345' [OK]

Hint: Count start position carefully, length matches digits needed [OK]

Common Mistakes:

• Starting extraction at wrong position
• Extracting fewer or more characters
• Confusing character positions