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

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

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

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

Hint

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

Practice

1. Why is string manipulation commonly used in databases like MySQL?

easy

A. To perform complex mathematical calculations

B. To clean, extract, and format text data efficiently

C. To store large binary files

D. To manage user permissions

Solution

1. Step 1: Understand the role of string manipulation

   String manipulation helps in handling text data by cleaning, extracting parts, and formatting it.

2. Step 2: Identify the correct use case in databases

   Databases often store text data that needs to be processed, so string functions are essential.

3. Final Answer:

   To clean, extract, and format text data efficiently -> Option B

4. Quick Check:

   String manipulation = text handling [OK]

Hint: String functions handle text, not numbers or files [OK]

Common Mistakes:

• Confusing string manipulation with numeric calculations
• Thinking string functions manage files or permissions

2. Which of the following is the correct syntax to convert a string to uppercase in MySQL?

easy

A. UPCASE('hello')

B. TOUPPER('hello')

C. UPPER('hello')

D. MAKEUPPER('hello')

Solution

1. Step 1: Recall MySQL string functions

   MySQL uses UPPER() to convert text to uppercase.

2. Step 2: Check syntax correctness

   UPPER('hello') is valid; other options are not recognized functions.

3. Final Answer:

   UPPER('hello') -> Option C

4. Quick Check:

   UPPER() = uppercase conversion [OK]

Hint: Use UPPER() to change text to uppercase [OK]

Common Mistakes:

• Using non-existent functions like TOUPPER or UPCASE
• Missing parentheses around the string

3. What will be the result of this MySQL query?
SELECT CONCAT('Data', 'Base') AS result;

medium

A. 'Database'

B. 'Data Base'

C. 'Data+Base'

D. 'Data,Base'

Solution

1. Step 1: Understand CONCAT function

   CONCAT joins strings directly without spaces or symbols.

2. Step 2: Apply CONCAT to given strings

   Concatenating 'Data' and 'Base' results in 'Database'.

3. Final Answer:

   'Database' -> Option A

4. Quick Check:

   CONCAT('Data','Base') = 'Database' [OK]

Hint: CONCAT joins strings exactly as given [OK]

Common Mistakes:

• Assuming CONCAT adds spaces or symbols automatically
• Confusing CONCAT with CONCAT_WS which adds separators

4. Identify the error in this MySQL query:
SELECT SUBSTRING('Hello World', 7, 5 FROM dual);

medium

A. Incorrect use of FROM clause in SUBSTRING function

B. Missing closing parenthesis

C. SUBSTRING function requires only two arguments

D. String literal must be in double quotes

Solution

1. Step 1: Review SUBSTRING syntax in MySQL

   SUBSTRING(string, start, length) does not use FROM clause inside function.

2. Step 2: Identify misuse of FROM clause

   FROM dual is used in Oracle, not inside SUBSTRING in MySQL.

3. Final Answer:

   Incorrect use of FROM clause in SUBSTRING function -> Option A

4. Quick Check:

   SUBSTRING syntax = no FROM inside function [OK]

Hint: SUBSTRING() uses commas, no FROM inside parentheses [OK]

Common Mistakes:

• Adding FROM inside SUBSTRING arguments
• Confusing MySQL syntax with Oracle SQL

5. You want to extract the domain name from email addresses stored in a MySQL table column email. Which query correctly extracts the domain part after '@'?

hard

A. SELECT LOWER(email) AS domain FROM users;

B. SELECT SUBSTRING(email, 1, LOCATE('@', email)) AS domain FROM users;

C. SELECT CONCAT('@', email) AS domain FROM users;

D. SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users;

Solution

1. Step 1: Understand the goal to extract domain after '@'

   The domain starts right after the '@' character in the email string.

2. Step 2: Use LOCATE to find '@' position and SUBSTRING to extract after it

   LOCATE('@', email) finds '@' position; adding 1 moves to domain start; SUBSTRING extracts from there to end.

3. Final Answer:

   SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users; -> Option D

4. Quick Check:

   Extract domain = SUBSTRING after '@' [OK]

Hint: Use LOCATE('@', email)+1 with SUBSTRING to get domain [OK]

Common Mistakes:

• Extracting before '@' instead of after
• Concatenating '@' instead of extracting domain
• Using LOWER() which only changes case