Lesson 04 Demo 02
Perform String Functions in MySQL
Objective: To demonstrate how to use string functions for modifying the text data
in MySQL
Tools required: MySQL Workbench
Prerequisites: None
Steps to be followed:
1. Log in to the MySQL Database
2. Create a Database and Table
3. Use the CONCATENATE function
4. Use the LENGTH function
5. Use the Uppercase function
6. Use the SUBSTRING function
7. Use the TRIM function
8. Use the REPLACE function
9. Use the CHAR_LENGTH function
Step 1: Log in to the MySQL Database
1.1 Connect to your MySQL server using a MySQL client or command-line
interface.
Step 2: Create a Database and Table
2.1 Create the employees_database by executing the following SQL
statement and using the Database
SQL Code:
CREATE DATABASE IF NOT EXISTS employees_database;
USE employees_database;
2.2 Create the employee_info table within the employees_database with
columns for employee ID (emp_id), first_name VARCHAR(100),last_name
VARCHAR(100):
SQL Code:
CREATE TABLE IF NOT EXISTS employee_info (
emp_id INT PRIMARY KEY,
first_name VARCHAR(100),
last_name VARCHAR(100));
2.3 Insert sample data into the employee_info table:
SQL Code:
INSERT INTO employee_info (emp_id, first_name, last_name) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Bob', 'Johnson');
Step 3: Use the CONCATENATE function
3.1 Combine the first_name and last_name columns into a full name for
each employee using the CONCAT() function
SQL Code:
SELECT emp_id, CONCAT(first_name, ' ', last_name) AS full_name FROM
employee_info;
Step 4: Use the LENGTH function
4.1 Write a SELECT query using the LENGTH() function to determine the length
of the first_name and last_name strings for each employee
SQL Code:
SELECT emp_id, LENGTH(first_name) AS first_name_length,
LENGTH(last_name) AS last_name_length FROM employee_info;
Step 5: Use the Uppercase function
5.1 Convert the first_name and last_name strings to uppercase for each
employee
SQL Code:
SELECT emp_id, UPPER(first_name) AS upper_first_name,
UPPER(last_name) AS upper_last_name FROM employee_info;
Step 6: Use the SUBSTRING function
6.1 Extract a substring from the first_name column for each employee using
the SUBSTRING() function
SQL Code:
SELECT emp_id, SUBSTRING(first_name, 1, 2) AS substring_first_name
FROM employee_info;
Step 7: Use the TRIM function
7.1 Remove leading and trailing spaces from the first_name column for each
employee using the TRIM() function
SQL Code:
SELECT emp_id, TRIM(BOTH ' ' FROM first_name) AS trimmed_first_name
FROM employee_info;
Step 8: Use the REPLACE function
8.1 Replace occurrences of the letter 'o' with 'X' in the first_name column for
each employee using the REPLACE() function
SQL Code:
SELECT emp_id, REPLACE(first_name, 'o', 'X') AS replaced_first_name
FROM employee_info;
Step 9: Use the CHAR_LENGTH function
9.1 Write a SELECT query using the CHAR_LENGTH() function to determine the
number of characters in the first_name column for each employee
SQL Code:
SELECT emp_id, CHAR_LENGTH(first_name) AS char_length_first_name
FROM employee_info;
String functions are executed on a specific dataset using these procedures.
