MySQL SUBSTRING Function Explained: Syntax, Examples & Practical Uses

By 佐川 直弘 | Naohiro Sagawa (運営者)

※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　

1. What is the SUBSTRING Function?

The SUBSTRING function is a crucial function in MySQL for extracting substrings from a string. By using this function, you can retrieve only the necessary parts from the data within your database. For example, it is useful when extracting the domain part from a user’s email address or obtaining a specific section from a product code.

1.1 Basic Syntax

The basic syntax of the SUBSTRING function is as follows:

SUBSTRING(str, pos)
SUBSTRING(str, pos, len)

• str: The string from which to extract.
• pos: The starting position for extraction (1-based).
• len: The number of characters to extract (optional).

If pos is a positive value, it moves forward from the beginning to the specified position. If it’s a negative value, it counts from the end. If len is omitted, it extracts from the specified position to the end of the string.

1.2 Uses of the SUBSTRING Function

This function is used for adjusting the format of string data and extracting specific parts, which streamlines information retrieval and data processing in databases.

2. Basic Usage of the SUBSTRING Function

Let’s look at a simple example to understand the basic usage.

2.1 Extracting a Part of a String

The following query extracts 6 characters starting from the 3rd character of the string “Hello, World!”.

SELECT SUBSTRING('Hello, World!', 3, 6);

The result is "llo, W". Since pos is 3, it starts from the 3rd character, and because len is 6, 6 characters are extracted.

2.2 Omitting the Length Specification

If len is omitted, it extracts from the specified position to the end.

SELECT SUBSTRING('Hello, World!', 8);

The result is "World!", which retrieves from the 8th character to the end.

2.3 Specifying a Negative Position

Using a negative value allows you to specify the position from the end of the string.

SELECT SUBSTRING('Hello, World!', -5);

This query returns "orld!", retrieving 5 characters from the end.

3. Practical Applications of the SUBSTRING Function

The SUBSTRING function is frequently used in real-world data manipulation. Here are a few application examples.

3.1 Extracting the Domain from an Email Address

Combine SUBSTRING and LOCATE to extract the domain part from an email address.

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

This query extracts the string after “@” to get only the domain part.

3.2 Extracting a Part of a Product Code

Here’s an example of extracting a specific part of a product code.

SELECT product_code, SUBSTRING(product_code, 5, 4) AS product_id FROM products;

In this query, 4 characters are extracted starting from the 5th character of the product code and displayed as a new column named product_id.

3.3 Using in Subqueries

By combining with subqueries, you can extract data based on complex conditions.

SELECT id, SUBSTRING(description, 1, 10) AS short_desc FROM (SELECT * FROM products WHERE category = 'Electronics') AS sub;

This query extracts the first 10 characters of the description from products where the category is ‘Electronics’.

4. Comparison with Other String Manipulation Functions

Other functions that can be used for similar purposes as the SUBSTRING function include LEFT, RIGHT, and SUBSTR.

4.1 LEFT and RIGHT Functions

• LEFT(str, len): Retrieves the specified number of characters from the beginning of a string.
• RIGHT(str, len): Retrieves the specified number of characters from the end of a string.

SELECT LEFT('Hello, World!', 5);  -- "Hello"
SELECT RIGHT('Hello, World!', 6); -- "World!"

These functions are useful when you need to get a substring from a specific end of the string.

4.2 SUBSTR Function

SUBSTR is an alias for SUBSTRING and can be used in the same way.

SELECT SUBSTR('Hello, World!', 8); -- "World!"

This query returns "World!", just like SUBSTRING.

5. Advanced Applications and Optimization of the SUBSTRING Function

Let’s discuss more advanced uses and optimization techniques.

5.1 Performance Optimization

Using SUBSTRING on large datasets can potentially impact performance. It’s important to create indexes as needed and review the query execution plan. Additionally, if you frequently extract the same substrings, consider caching the results.

5.2 Using in the WHERE Clause

You can use SUBSTRING in the WHERE clause to perform conditional searches based on substrings.

SELECT * FROM products WHERE SUBSTRING(product_code, 1, 3) = 'ABC';

This query searches for products where the first 3 characters of the product_code are ‘ABC’.

6. Examples and Best Practices for the SUBSTRING Function

Here are some examples of how to use the SUBSTRING function in practical scenarios, along with best practices.

6.1 Sample Code

The following example demonstrates how to split a customer’s full name into first name and last name.

SELECT name, SUBSTRING(name, 1, LOCATE(' ', name) - 1) AS first_name,
       SUBSTRING(name, LOCATE(' ', name) + 1) AS last_name
FROM customers;

This query extracts the first name and last name from a space-separated full name.

6.2 Best Practices

• Extract the Minimum Necessary: Minimize the length of the substring you extract with SUBSTRING to reduce the impact on performance.
• Pay Attention to Data Types: When applying SUBSTRING to numerical data, it’s necessary to explicitly cast it to a string.
• Index Application: Be aware that when using SUBSTRING in the WHERE clause, indexes might not be effective, so pay attention to query performance.

7. Error Handling and Version Differences

Let’s touch upon error handling when using SUBSTRING and the differences that may exist between MySQL versions.

7.1 Error Handling

When using SUBSTRING, if the specified position is outside the bounds of the string, an empty string is returned. This is not an error, so it is recommended to add logic to check the results beforehand.

7.2 Version Differences

The behavior of the SUBSTRING function might vary depending on the MySQL version. For example, some older versions might handle multi-byte characters differently. Ensure compatibility between versions and take appropriate measures if necessary.