Finding the Second or Third Index of a Substring in MySQL

When working with strings in a database, it's often necessary to locate the position of a particular substring. In cases where a simple LIKE query is insufficient, you may need a way to precisely identify the index of a specific occurrence of that substring.

Problem:
You have a space-separated string and need to extract a particular section of the string based on its relative position. For instance, given the string "AAAA BBBB CCCC DDDD EEE," you want to extract the substring "CCCC."

Solution:
MySQL provides a powerful function called SUBSTRING_INDEX that allows you to find the nth occurrence of a substring within a larger string. To find the second (or third) space in a string, you can utilize nested SUBSTRING_INDEX calls.

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(field, ' ', 3), ' ', -1)
FROM table

In the above query, the inner SUBSTRING_INDEX call extracts the substring up to the third space in the field column, giving you "AAAA BBBB CCCC." The outer SUBSTRING_INDEX call then isolates the substring after that third space, which in this case is "CCCC."

By nesting multiple SUBSTRING_INDEX calls, you can continue to identify subsequent occurrences of the delimiter and extract the desired substrings as needed. This versatile function provides a convenient and efficient way to handle complex string manipulation tasks in MySQL.