How to Split String in MySQL

Use SUBSTRING_INDEX() to extract text on either side of a delimiter in MySQL. To return every part as its own row, combine it with a number sequence. If the values are already stored as a JSON array, use JSON extraction to read its elements; JSON functions do not split arbitrary delimited text.

Use SUBSTRING_INDEX() to Extract Text Around a Delimiter

SUBSTRING_INDEX(str, delim, count) returns the part of str before the countth occurrence of delim when count is positive, or the part to the right of the corresponding occurrence when count is negative. The delimiter is a string and may contain more than one character. If the requested occurrence is absent, the function returns the whole input string.

For example, with the comma-delimited value apple,banana,cherry, SUBSTRING_INDEX(value, ',', 1) returns apple, while SUBSTRING_INDEX(value, ',', -1) returns cherry. A nested call can isolate a middle field: SUBSTRING_INDEX(SUBSTRING_INDEX(value, ',', 2), ',', -1) returns banana. Replace value with a column name to apply the expression to table data.

For a line break stored between two address parts, use SUBSTRING_INDEX(address, '\n', 1) for the text before the first newline and SUBSTRING_INDEX(address, '\n', -1) for the text after the last newline. In a MySQL string literal, \n represents a newline under the usual escape handling; SQL modes that disable backslash escapes require the delimiter to be expressed accordingly.

The SQL snippets here are shown inline because the configured article runtime does not execute MySQL SQL. Run them against your MySQL database after replacing the sample value or column with your data.

Split a Delimited String into Rows

To return one value per row, apply the nested SUBSTRING_INDEX() expression to a sequence of integers. This illustrative query uses a derived sequence from 1 through 5:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,cherry', ',', numbers.n), ',', -1) AS fruit FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS numbers WHERE numbers.n <= LENGTH('apple,banana,cherry') - LENGTH(REPLACE('apple,banana,cherry', ',', '')) + 1;

For this input, the expression selects the first, second, and third fields; the WHERE condition counts commas and adds one to determine how many fields to return. A result table would contain apple, banana, and cherry, one value per row. This query is a MySQL example and is not executed by the configured runtime.

The sequence is bounded: this derived table can produce at most five rows. Extend it or join to a numbers table if an input can contain more than five parts. The count expression assumes a nonempty input and treats every comma as a separator; decide how your application should handle empty input, repeated separators, or values containing a comma before applying this pattern. For frequently split data, storing each value in its own row is usually easier to query and maintain.

Read Elements from a JSON Array

When a column already contains a valid JSON array, use JSON functions to read its elements by zero-based index. This is array access, not a general-purpose way to split plain delimited text. The following expressions illustrate the element paths for an array such as ["apple","banana","cherry"]: JSON_UNQUOTE(JSON_EXTRACT(json_column, '$[0]')), JSON_UNQUOTE(JSON_EXTRACT(json_column, '$[1]')), and JSON_UNQUOTE(JSON_EXTRACT(json_column, '$[2]')). They return apple, banana, and cherry, respectively, without the JSON string quotes. Replace json_column with your JSON column. The example assumes valid JSON containing an array with those positions; verify function availability against the MySQL version used by your server.

Choose the Right MySQL Approach

• Use SUBSTRING_INDEX() when you need one substring before or after a known delimiter occurrence.
• Use a number sequence with nested SUBSTRING_INDEX() calls when a query needs each delimited part as a row. Make sure the sequence is long enough for the largest input and define how empty values and repeated delimiters should be handled.
• Use JSON extraction when the data is already a valid JSON array and you need elements at known indexes. It does not convert an arbitrary comma-separated string into a JSON array.

For data that is repeatedly filtered, joined, or updated by individual values, consider storing those values as separate rows instead of splitting a string for every query.