MySQL SUBSTRING() Function

Introduction

In this chapter, we will learn about the SUBSTRING() function in MySQL. The SUBSTRING() function is used to extract a portion of a string. This function is useful for tasks such as extracting specific parts of text data, formatting data, or analyzing text patterns. We will cover the syntax for the SUBSTRING() function, a complete example of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the SUBSTRING() function in MySQL is:

SELECT SUBSTRING(string, start_position, length);

• string: The string from which to extract the substring.
• start_position: The position to start extracting the substring. The first position is 1.
• length: The number of characters to extract.

Complete Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the SUBSTRING() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

INSERT INTO employees (first_name, last_name, email) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com'),
('Priya', 'Singh', 'priya.singh@example.com'),
('Amit', 'Kumar', 'amit.kumar@example.com');

2. Using SUBSTRING() to Extract Substrings

We will use the SUBSTRING() function to extract the first three characters from the first names of employees.

SELECT first_name, SUBSTRING(first_name, 1, 3) AS substring_first_name
FROM employees;

Output:

3. Extracting Domain from Email Addresses

We will use the SUBSTRING() function to extract the domain part of the email addresses.

SELECT email, SUBSTRING(email, INSTR(email, '@') + 1) AS domain
FROM employees;

Output:

4. Combining SUBSTRING() with Other Functions

We can combine the SUBSTRING() function with other string functions to perform more complex text manipulations.

Example: Extract the Last Name from the Email Address

SELECT email, SUBSTRING(email, 1, INSTR(email, '.') - 1) AS last_name
FROM employees;

Output:

Important Considerations

• Start Position: The start_position is 1-based, meaning the first character in the string is at position 1.
• Negative Length: If the length is negative, the SUBSTRING() function returns an empty string.
• Performance: Using the SUBSTRING() function on large datasets can impact performance. Optimize your queries by using it only when necessary and considering the impact on query execution time.
• Data Integrity: Ensure that extracting substrings does not affect the integrity of your data, especially when used in combination with other string functions.

Conclusion

The SUBSTRING() function in MySQL is used for extracting portions of a string. This chapter covered the syntax for using the SUBSTRING() function, provided a complete example of its usage, and discussed important considerations.