MySQL SUBSTRING Function: Syntax, Examples, and Best Practices

1. What is the SUBSTRING Function?

The SUBSTRING function in MySQL is an essential tool for extracting a portion of a string. With this function, you can retrieve only the necessary part of data from a database. For example, it’s useful for extracting the domain from a user’s email address or retrieving 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 target string to extract from.
• pos: The starting position (1-based index).
• len: The number of characters to extract (optional).

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

1.2 Use Cases of the SUBSTRING Function

This function is used for formatting string data or extracting specific parts, making data retrieval and processing in databases more efficient.

2. Basic Usage of the SUBSTRING Function

Let’s look at a simple example to understand how it works.

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 at the 3rd character, and with len set to 6, it extracts 6 characters.

2.2 Omitting the Length

If you omit len, it will extract from the specified position to the end of the string.

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

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

2.3 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!", 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 examples.

3.1 Extracting the 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 retrieves the string after the “@” symbol, giving you only the domain part.

3.2 Extracting 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;

This query extracts 4 characters starting from the 5th character of the product code and displays it as a new column product_id.

3.3 Using with Subqueries

Combining it with subqueries allows you to extract data under 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 category is ‘Electronics’.

4. Comparison with Other String Functions

Other functions with similar purposes to SUBSTRING include LEFT, RIGHT, and SUBSTR.

4.1 LEFT and RIGHT Functions

• LEFT(str, len): Retrieves the specified number of characters from the start 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 handy when you want to extract a portion of a string from a specific end.

4.2 SUBSTR Function

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

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

This query returns "World!" just like SUBSTRING.

5. Advanced Use and Optimization of the SUBSTRING Function

Here’s how to use SUBSTRING more efficiently and optimize its performance.

5.1 Performance Optimization

Using SUBSTRING on large datasets may impact performance. Consider creating indexes and checking the query execution plan when necessary. If you frequently extract the same substring, caching the results can also help.

5.2 Using in the WHERE Clause

You can use SUBSTRING in a WHERE clause to search based on a portion of a string.

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

This query retrieves products whose product_code starts with ‘ABC’.

6. Examples and Best Practices for the SUBSTRING Function

Here are practical examples and best practices for using SUBSTRING.

6.1 Sample Code

The following sample splits a customer’s full name into first and last names.

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 and last names from a full name separated by a space.

6.2 Best Practices

• Minimal Extraction: Extract only what’s necessary with SUBSTRING to minimize performance impact.
• Mind the Data Type: When applying SUBSTRING to numeric data, explicitly cast it to a string first.
• Index Considerations: Using SUBSTRING in a WHERE clause may prevent indexes from being used, so check your query performance.

7. Error Handling and Version Differences

Let’s cover error handling and differences in SUBSTRING behavior across MySQL versions.

7.1 Error Handling

If the specified position in SUBSTRING is outside the range of the string, it returns an empty string. Since this is not an error, it’s recommended to add logic to check results beforehand.

7.2 Version Differences

Depending on your MySQL version, the behavior of SUBSTRING may vary. For example, some older versions handle multibyte characters differently. Check for compatibility between versions and apply appropriate measures as needed.