Extract Substrings in MySQL

MySQL provides several functions to extract substrings, with SUBSTRING() and LEFT() and RIGHT() functions being the most common. Extracting substrings in MySQL involves retrieving a specific part of a string based on position and length.

Extracting substrings is helpful for tasks like obtaining initials, isolating file extensions, or filtering parts of longer strings.

In this tutorial, we will go through examples on how to extract the substring using SUBSTRING() and LEFT() and RIGHT() functions in MySQL.

Methods for Extracting Substrings in MySQL

MySQL provides multiple functions to extract parts of strings:

  • SUBSTRING(string, position, length): Extracts a substring starting from the given position for the specified length.
  • LEFT(string, length): Extracts the specified number of characters from the beginning of the string.
  • RIGHT(string, length): Extracts the specified number of characters from the end of the string.

Setup for Examples: Creating the Table

To demonstrate substring extraction, we’ll use a users table with fields username and email.

1. Create a new database called company_db:

</>
Copy
CREATE DATABASE company_db;

2. Select the company_db database:

</>
Copy
USE company_db;

3. Create the users table with username and email fields:

</>
Copy
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100)
);

4. Insert sample data into the users table:

</>
Copy
INSERT INTO users (username, email)
VALUES 
('john_doe', 'john.doe@example.com'),
('jane_smith', 'jane.smith@example.com'),
('emily_clark', 'emily.clark@example.com');
Extract Substrings in MySQL - Setup for examples

Examples: Extracting Substrings in MySQL

With the table set up, we can now explore various examples of extracting substrings.

users table data

1. Extracting Domain from Email Address Using SUBSTRING()

To retrieve the domain from the email field, starting after the “@” symbol:

</>
Copy
SELECT email, 
       SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;

This query uses SUBSTRING() with LOCATE() to extract the domain from each email, starting right after “@”.

Extract Substrings in MySQL - Example - Extracting Domain from Email Address

2. Extracting Initials from Username Using LEFT()

To extract the first two characters from each username as initials:

</>
Copy
SELECT username, LEFT(username, 2) AS initials
FROM users;

This query retrieves the first two characters of each username, which could be used as initials.

Extract Substrings in MySQL - Example - Extracting substring using LEFT()

3. Extracting the Last Four Characters of a Username Using RIGHT()

To retrieve the last four characters of each username:

</>
Copy
SELECT username, RIGHT(username, 4) AS user_suffix
FROM users;

This query returns the last four characters of each username, which might represent a unique code or identifier.

Extract Substrings in MySQL - Example - Extracting substring Using RIGHT()

Conclusion

MySQL provides versatile substring functions, including SUBSTRING(), LEFT(), and RIGHT(). These functions allow you to isolate parts of strings based on position and length, making them valuable for data extraction, formatting, and manipulation tasks.