MySQL SUBSTRING() Explained: Extract Parts of a String with Examples

1. What Is the SUBSTRING Function?

The SUBSTRING function is an important MySQL function used to extract a substring from a string. With this function, you can pull out only the part you need from data stored in a database. For example, it is useful when you want to extract the domain part from a user’s email address, or retrieve 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 to extract from.
• pos: The starting position (starts from 1).
• len: The number of characters to extract (optional).

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

1.2 Use Cases for the SUBSTRING Function

This function is used to format string data and extract specific parts, making it easier to search and process information efficiently in a database.

2. Basic Usage of the SUBSTRING Function

To understand the basics, let’s look at some simple examples.

2.1 Extracting 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, extraction starts from the 3rd character, and because len is 6, 6 characters are extracted.

2.2 Omitting the Length Parameter

If you omit len, MySQL extracts from the specified position to the end of the string.

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

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

2.3 Using a Negative Position

If you use a negative value, you can specify the position from the end of the string.

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

This query returns "orld!", extracting the last 5 characters.

3. Practical Applications of the SUBSTRING Function

The SUBSTRING function is frequently used in real-world data operations. Here are some practical examples.

3.1 Extracting a Domain from an Email Address

By combining SUBSTRING with LOCATE, you can 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 “@” and returns only the domain part.

3.2 Extracting Part of a Product Code

This example extracts a specific section of a product code.

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

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

3.3 Using It in a Subquery

By combining it with a subquery, you can extract data under more 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 description from products where category is ‘Electronics’.

4. Comparison with Other String Functions

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

4.1 LEFT and RIGHT Functions

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

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

These functions are convenient when you want to extract a substring from the beginning or the end of a string.

4.2 SUBSTR Function

SUBSTR is an alias for SUBSTRING, so it can be used in the same way.

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

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

5. Advanced Usage and Optimization for the SUBSTRING Function

This section explains more advanced usage and optimization techniques.

5.1 Performance Optimization

Using SUBSTRING on large datasets may affect performance. If necessary, create indexes and check the query execution plan. Also, if you frequently extract the same substring, consider caching the result.

5.2 Using It in a WHERE Clause

You can use SUBSTRING in a WHERE clause to filter records based on a substring condition.

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

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

6. Examples and Best Practices for the SUBSTRING Function

Here are practical examples of using SUBSTRING and best practices for real work.

6.1 Sample Code

The following sample splits 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;

In this query, first and last names are extracted from a full name separated by a space.

6.2 Best Practices

• Extract only what you need: Keep the substring length as small as possible to reduce performance impact.
• Pay attention to data types: If you apply SUBSTRING to numeric data, you may need to explicitly cast it to a string.
• Consider index usage: When using SUBSTRING in a WHERE clause, indexes may not be used effectively, so watch query performance carefully.

7. Error Handling and Version Differences

This section covers error handling when using SUBSTRING and differences between MySQL versions.

7.1 Error Handling

If the specified position is outside the range of the string, SUBSTRING returns an empty string. Since this is not an error, it is recommended to add logic to validate the result in advance if needed.

7.2 Version Differences

Depending on the MySQL version, the behavior of the SUBSTRING function may differ. For example, some older versions may handle multi-byte characters differently. Check compatibility between versions and apply appropriate measures when necessary.