Why string manipulation is common in MySQL - See It in Action

Why String Manipulation is Common in SQL

📖 Scenario: Imagine you work in a company database team. You often get messy customer data with names, emails, and phone numbers all mixed up. To clean and organize this data, you need to change and check parts of text fields. This is why string manipulation is very common in databases.

🎯 Goal: You will create a simple table with customer names and emails, then write queries to extract and modify parts of these strings. This will show why string manipulation is important in real database work.

📋 What You'll Learn

Create a table called customers with columns id, name, and email

Insert three specific customer records with exact names and emails

Write a query to select the first 5 characters of each customer's name

Write a query to find customers whose email ends with @example.com

💡 Why This Matters

🌍 Real World

Cleaning and organizing customer data often requires changing and checking parts of text fields like names and emails.

💼 Career

Database professionals frequently use string functions to prepare data for reports, analysis, and application use.

1

Create the customers table and insert data

Create a table called customers with columns id as integer primary key, name as varchar(50), and email as varchar(100). Then insert these three rows exactly: (1, 'Alice Johnson', '[e-mail]'), (2, 'Bob Smith', '[e-mail]'), (3, 'Carol White', '[e-mail]').

MySQL

-- Create the customers table and insert the three rows
-- Your code here

Need a hint?

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

2

Add a query to extract the first 5 characters of names

Write a SELECT query that returns the id and the first 5 characters of the name column as short_name from the customers table. Use the LEFT(name, 5) function.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice Johnson', '[e-mail]'),
(2, 'Bob Smith', '[e-mail]'),
(3, 'Carol White', '[e-mail]');

-- Write a SELECT query to get id and first 5 characters of name as short_name
-- Your code here

Need a hint?

Use the LEFT() function to get the first characters of a string.

3

Add a query to find customers with emails ending in '@example.com'

Write a SELECT query that returns all columns from customers where the email ends with '@example.com'. Use the LIKE '[e-mail]' condition.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice Johnson', '[e-mail]'),
(2, 'Bob Smith', '[e-mail]'),
(3, 'Carol White', '[e-mail]');

SELECT id, LEFT(name, 5) AS short_name FROM customers;

-- Write a SELECT query to get all columns for customers with email ending '@example.com'
-- Your code here

Need a hint?

Use LIKE '[e-mail]' to filter emails ending with that domain.

4

Add a query to replace 'example.com' with 'mydomain.com' in emails

Write a SELECT query that returns id and the email column but with 'example.com' replaced by 'mydomain.com'. Use the REPLACE(email, 'example.com', 'mydomain.com') function and alias it as new_email.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO customers (id, name, email) VALUES
(1, 'Alice Johnson', '[e-mail]'),
(2, 'Bob Smith', '[e-mail]'),
(3, 'Carol White', '[e-mail]');

SELECT id, LEFT(name, 5) AS short_name FROM customers;

SELECT * FROM customers WHERE email LIKE '[e-mail]';

-- Write a SELECT query to get id and email with 'example.com' replaced by 'mydomain.com' as new_email
-- Your code here

Need a hint?

Use the REPLACE() function to change parts of a string.