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

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

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

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

Hint

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

Practice

1. What does the MySQL function LEFT('Hello World', 5) return?

easy

A. 'World'

B. 'Hello'

C. 'Hello World'

D. 'WorldHello'

Solution

1. Step 1: Understand LEFT function

   The LEFT function extracts a specified number of characters from the start (left side) of a string.

2. Step 2: Apply LEFT to 'Hello World'

   Extracting 5 characters from the start of 'Hello World' gives 'Hello'.

3. Final Answer:

   'Hello' -> Option B

4. Quick Check:

   LEFT('Hello World', 5) = 'Hello' [OK]

Hint: LEFT gets characters from the start of the string [OK]

Common Mistakes:

• Confusing LEFT with RIGHT function
• Expecting the whole string instead of part
• Using wrong number of characters

2. Which of the following is the correct syntax to extract 3 characters starting from the 2nd character in MySQL?

easy

A. RIGHT('abcdef', 2, 3)

B. LEFT('abcdef', 2, 3)

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

D. SUBSTRING('abcdef', 3, 2)

Solution

1. Step 1: Recall SUBSTRING syntax

   SUBSTRING(string, start_position, length) extracts length characters starting at start_position.

2. Step 2: Check each option

   SUBSTRING('abcdef', 2, 3) uses SUBSTRING with start=2 and length=3, which is correct. LEFT and RIGHT do not accept three arguments.

3. Final Answer:

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

4. Quick Check:

   Correct SUBSTRING syntax = SUBSTRING('abcdef', 2, 3) [OK]

Hint: SUBSTRING needs start and length; LEFT/RIGHT only need length [OK]

Common Mistakes:

• Using LEFT or RIGHT with three arguments
• Swapping start and length values
• Starting count from zero instead of one

3. What is the result of this query?
SELECT SUBSTRING('Database', 5, 3) AS part1, LEFT('Database', 4) AS part2, RIGHT('Database', 2) AS part3;

medium

A. part1='bas', part2='Data', part3='se'

B. part1='base', part2='Data', part3='se'

C. part1='tab', part2='D', part3='se'

D. part1='bas', part2='Datab', part3='se'

Solution

1. Step 1: Calculate SUBSTRING('Database', 5, 3)

   Starting at position 5: 'D'(1), 'a'(2), 't'(3), 'a'(4), 'b'(5), 'a'(6), 's'(7), 'e'(8). Characters 5 to 7 are 'b', 'a', 's' -> 'bas'.

2. Step 2: Calculate LEFT('Database', 4) and RIGHT('Database', 2)

   LEFT takes first 4 chars: 'D', 'a', 't', 'a' -> 'Data'. RIGHT takes last 2 chars: 's', 'e' -> 'se'.

3. Final Answer:

   part1='bas', part2='Data', part3='se' -> Option A

4. Quick Check:

   SUBSTRING, LEFT, RIGHT results match part1='bas', part2='Data', part3='se' [OK]

Hint: Count characters carefully starting at 1 for SUBSTRING [OK]

Common Mistakes:

• Counting start position from zero
• Mixing up LEFT and RIGHT results
• Extracting wrong substring length

4. Identify the error in this query:
SELECT LEFT('Example', 3, 2) AS result;

medium

A. LEFT function does not accept three arguments

B. String 'Example' is too short

C. Missing FROM clause

D. RIGHT function should be used instead

Solution

1. Step 1: Check LEFT function syntax

   LEFT(string, length) accepts only two arguments: the string and number of characters from the left.

2. Step 2: Analyze the query

   The query uses three arguments in LEFT, which is invalid syntax and causes an error.

3. Final Answer:

   LEFT function does not accept three arguments -> Option A

4. Quick Check:

   LEFT syntax error = LEFT function does not accept three arguments [OK]

Hint: LEFT only needs string and length, no extra arguments [OK]

Common Mistakes:

• Adding extra arguments to LEFT or RIGHT
• Confusing LEFT with SUBSTRING syntax
• Ignoring syntax errors in function calls

5. You have a column full_name with values like 'John Smith'. Which query extracts the last 5 characters of full_name to get the last name part using MySQL functions?

hard

A. SELECT SUBSTRING(full_name, 6, 5) AS last_name FROM users;

B. SELECT LEFT(full_name, 5) AS last_name FROM users;

C. SELECT SUBSTRING(full_name, -5) AS last_name FROM users;

D. SELECT RIGHT(full_name, 5) AS last_name FROM users;

Solution

1. Step 1: Understand the requirement

   We want the last 5 characters of the full_name, which is the last name part 'Smith' in 'John Smith'.

2. Step 2: Choose the correct function

   RIGHT(string, length) extracts characters from the end. RIGHT(full_name, 5) returns last 5 characters.

3. Step 3: Check other options

   LEFT extracts from start, SUBSTRING with negative start is invalid in MySQL, and SUBSTRING with fixed start 6 may fail if names vary.

4. Final Answer:

   SELECT RIGHT(full_name, 5) AS last_name FROM users; -> Option D

5. Quick Check:

   RIGHT gets last characters = SELECT RIGHT(full_name, 5) AS last_name FROM users; [OK]

Hint: Use RIGHT to get characters from the end of a string [OK]

Common Mistakes:

• Using LEFT instead of RIGHT for last characters
• Using SUBSTRING with negative start (not supported)
• Assuming fixed position for last name