How to Split String in MySQL

This tutorial will introduce how to split a string in MySQL database.

Firstly, we use the SUBSTRING_INDEX() function to split a string. It is a predefined function, and the user does not have to define it before using it in MySQL.

This function helps us segregate the string into segments that can be used to extract important information.

The syntax of this function can be understood as follows.

SUBSTRING_INDEX(expr, delimiter, counter);

This method needs us to give three values, as seen above. These are expr also known as expression, delimiter that tells the method to stop the execution, and counter that represents the occurrence count of the delimiter. Below introduces how to pass 3 parameters.

• The premier requirement will be the input string under consideration. We can pass the string here or the column name of a particular table from a database. This is denoted by expr in the above syntax.
• The next requirement is the delimiter that tells the function when to stop execution.
• Here, we can pass a number. This number represents the occurrence of the particular delimiter.

Let’s see an example of the SUBSTRING_INDEX() in action.

Suppose you have an address string as Satra Park, X-10202 \n Borivali West, Mumbai.

Let us aim at segregating the aforementioned string based on the occurrence of the newline keyword, also written as \n.

We can implement this using the following query.

SELECT 
  SUBSTRING_INDEX("Satra Park, X-10202 \n Borivali West, Mumbai", '\n', 1) 
    AS address_one,
  SUBSTRING_INDEX("Satra Park, X-10202 \n Borivali West, Mumbai", '\n', -1) 
    AS address_two;

The query mentioned above will give us the following output.

address_one					address_two
Satra Park, X-10202         Borivali West, Mumbai

The SUBSTRING_INDEX() function goes through the input string for the delimiter characters, after which it comes up with a string based on the appearance of the delimiter passed as the last requirement.

Thus, with the help of the SUBSTRING_INDEX() function, we can split a string in MySQL.