MySQL String Split: Methods & Examples

There are several methods for splitting strings in MySQL.

1. The SUBSTRING_INDEX function can split a string based on a specified delimiter and return the substring before or after a specified position. For example, you can use the SUBSTRING_INDEX function to retrieve the first, last, or specific position substring from a string.

SELECT SUBSTRING_INDEX('apple,orange,banana', ',', 1); -- 返回apple
SELECT SUBSTRING_INDEX('apple,orange,banana', ',', -1); -- 返回banana

1. By using the SUBSTRING function, you can extract a substring from a string based on a specified starting position and length. By combining the SUBSTRING function with the LOCATE function (used to find the position of a substring within a string), you can split a string based on a specific delimiter.

SELECT SUBSTRING('apple,orange,banana', 1, LOCATE(',', 'apple,orange,banana') - 1); -- 返回apple
SELECT SUBSTRING('apple,orange,banana', LOCATE(',', 'apple,orange,banana') + 1); -- 返回orange,banana

1. Using regular expression functions: MySQL offers some regular expression functions, such as REGEXP_SUBSTR and REGEXP_REPLACE, that can be used to match and manipulate strings based on regular expressions. By using regular expressions to match delimiters, it is possible to split strings based on specific separators.

SELECT REGEXP_SUBSTR('apple,orange,banana', '[^,]+', 1, 1); -- 返回apple
SELECT REGEXP_REPLACE('apple,orange,banana', '^[^,]+,', ''); -- 返回orange,banana

These are some commonly used methods for splitting strings in MySQL. Depending on your specific needs, you can choose the appropriate method to achieve the string splitting operation.