SUBSTRING and LEFT/RIGHT in MySQL - Mini Project: Build & Apply

Extracting Parts of Strings with SUBSTRING and LEFT/RIGHT in MySQL

📖 Scenario: You work in a small online bookstore. The database has a table books with a column isbn that stores ISBN codes as strings. You want to extract parts of these ISBN codes to analyze publisher codes and group codes.

🎯 Goal: Build SQL queries step-by-step to extract specific parts of the isbn strings using SUBSTRING, LEFT, and RIGHT functions.

📋 What You'll Learn

Create a table books with an isbn column and insert sample data

Define a variable for the length of the publisher code

Write a query using LEFT to get the publisher code from isbn

Write a query using SUBSTRING and RIGHT to get the group code from isbn

💡 Why This Matters

🌍 Real World

Extracting parts of strings like ISBN codes helps in categorizing books by publisher or group, which is common in retail and inventory management.

💼 Career

Knowing how to manipulate strings in SQL is essential for data cleaning, reporting, and preparing data for analysis in many database-related jobs.

1

Create the books table and insert sample ISBN data

Create a table called books with a column isbn of type VARCHAR(20). Insert these exact ISBN values into the table: '978-3-16-148410-0', '978-0-262-13472-9', and '978-1-4028-9462-6'.

MySQL

-- Create the books table and insert the ISBN values
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the ISBNs.

2

Define a variable for the publisher code length

Define a MySQL user variable called @publisher_length and set it to 3 to represent the length of the publisher code in the ISBN.

MySQL

CREATE TABLE books (isbn VARCHAR(20));
INSERT INTO books (isbn) VALUES
('978-3-16-148410-0'),
('978-0-262-13472-9'),
('978-1-4028-9462-6');
-- Define @publisher_length variable
-- Your code here

Need a hint?

Use SET @publisher_length = 3; to define the variable.

3

Extract the publisher code using LEFT

Write a SELECT query that uses the LEFT function on the isbn column to extract the first @publisher_length characters as the publisher code. Name this extracted column publisher_code.

MySQL

CREATE TABLE books (isbn VARCHAR(20));
INSERT INTO books (isbn) VALUES
('978-3-16-148410-0'),
('978-0-262-13472-9'),
('978-1-4028-9462-6');
SET @publisher_length = 3;
-- Select publisher_code using LEFT
-- Your code here

Need a hint?

Use LEFT(isbn, @publisher_length) in the SELECT statement.

4

Extract the group code using SUBSTRING and RIGHT

Write a SELECT query that extracts the group code from the isbn column. Use RIGHT to get the last 10 characters, then use SUBSTRING to get the first 2 characters of that result. Name this extracted column group_code.

MySQL

CREATE TABLE books (isbn VARCHAR(20));
INSERT INTO books (isbn) VALUES
('978-3-16-148410-0'),
('978-0-262-13472-9'),
('978-1-4028-9462-6');
SET @publisher_length = 3;
SELECT isbn, LEFT(isbn, @publisher_length) AS publisher_code FROM books;
-- Select group_code using SUBSTRING and RIGHT
-- Your code here

Need a hint?

Use RIGHT(isbn, 10) inside SUBSTRING(..., 1, 2) to get the group code.