The World of MySQL & MariaDB

Mastering String Functions in MySQL: SUBSTRING, LEFT, RIGHT & SUBSTRING_INDEX

Functions
目次

1. Basics of String Manipulation in MySQL

In database management, string manipulation is an essential skill for data processing and query optimization. MySQL provides convenient functions to extract and manipulate strings. In this article, we will focus on the important SUBSTRING function, along with other string manipulation methods, and explain practical use cases.

2. Basic String Extraction in MySQL – SUBSTRING Function

The SUBSTRING function in MySQL is one of the most commonly used functions to extract a portion of a string.

Basic Syntax of SUBSTRING

SUBSTRING(string, start_position, length)
  • string: The target string to extract from.
  • start_position: The starting position of the extraction (the first character is position 1).
  • length: The number of characters to extract. If omitted, it extracts from the start position to the end of the string.

Example: Basic Usage

SELECT SUBSTRING('Hello World', 2, 5);
This query extracts 5 characters starting from the second character of “Hello World,” resulting in “ello “.

Using Negative Values with SUBSTRING

By specifying a negative starting position, you can extract characters counted from the end of the string.
SELECT SUBSTRING('abcdefg', -3, 2);
This query returns “ef,” which are the third and fourth characters from the end.

3. Extracting Strings with LEFT and RIGHT Functions

Instead of SUBSTRING, you can use LEFT or RIGHT to retrieve a specific number of characters from the beginning or end of a string.

LEFT Function

The LEFT function extracts a specified number of characters from the left side of a string.
SELECT LEFT('abcdefg', 3);
This query returns “abc”.

RIGHT Function

The RIGHT function extracts a specified number of characters from the right side of a string.
SELECT RIGHT('abcdefg', 3);
This query returns “efg”. These functions are especially useful when you always need to extract a fixed number of characters from either side of a string.

4. Splitting Strings with SUBSTRING_INDEX

The SUBSTRING_INDEX function splits a string based on a specified delimiter and returns a particular part. This is especially useful for CSV data or fields containing multiple concatenated values.

Basic Syntax of SUBSTRING_INDEX

SUBSTRING_INDEX(string, delimiter, N)
  • string: The target string to operate on.
  • delimiter: The character used to split the string (e.g., a comma).
  • N: The number of parts to return. A positive value counts from the start, a negative value counts from the end.

Example: Usage

SELECT SUBSTRING_INDEX('apple,orange,banana', ',', 2);
This query retrieves “apple,orange” from the string “apple,orange,banana”.

5. Practical Applications: String Extraction in Database Operations

String manipulation is very useful in real-world database management. Below are some practical examples.

Extracting Part of a Product Name

The following query searches for product names ending with “Large”.
SELECT * FROM products WHERE SUBSTRING(name, -2, 2) = 'Large';
This way, you can extract records that match a specific condition at the end of a string.

Extracting Numeric Data for Calculations

Here is an example of extracting part of a numeric field for calculations:
SELECT name, price, SUBSTRING(price, -2, 2) * 5 AS total FROM products;
This query extracts the last two digits of the product price, multiplies them by 5, and displays the result as “total”.

6. Performance Optimization Tips

While string functions are powerful, they can impact performance in large databases. Below are some tips for improving performance.

Use Indexes Efficiently

When searching for parts of strings, creating appropriate indexes can significantly improve query speed. For example, when using the LIKE clause, queries like LIKE 'abc%' can use indexes effectively. However, queries like LIKE '%abc' (suffix matches) cannot use indexes, which may reduce performance.

String Operations on Large Datasets

If you frequently perform string operations on large tables, consider handling string manipulation at the application level. Offloading heavy processing from the database to the application can help reduce server load.

7. Conclusion

String manipulation in MySQL is a powerful tool for data extraction and reporting. By mastering functions such as SUBSTRING, LEFT, and RIGHT, you can easily extract the exact information you need. To optimize performance, it is important to configure indexes properly and design efficient processing methods. By mastering these techniques, you can further enhance your MySQL string manipulation skills. As a next step, consider learning about regular expressions and other advanced string operations.