How To MySQL Split String By Delimiter

MySql is an open-source tool for managing relations databases and servers. It is often known as RDBMS (relational database management system). It is used to handle databases in a simpler way that makes programmers use it extensively. Being open-source software, programmers can use it freely and also modify it. MySQL is a client-server model, which shows that system that has RDBMS installed and executed is known as a client. Every time they want to access a particular data, it is connected to the server of RDBMS, which is the client-server model. It is flexible and easy to use as it allows programmers to edit the source code according to their requirements along with updating to the commercial advanced version. MySQL is quite popular in handling databases among developers and programmers. When you are in a learning phase of MYSQL, you must want to know how to MYSQL split string by delimiter.

MySQL Split String

The substring_index() function is used to get a substring from the given string prior to numbers specified of delimiter occurrences.

The syntax of the function SUBSTRING_INDEX() function is given below:

SUBSTRING_INDEX(str,delimiter,n)

Where str stands for a string that needs to be extracted, the delimiter is basically a string that works like a delimiter, which is case-sensitive, and n denotes an integer that shows the number of delimiter occurrences. The ‘n’ can be positive and negative, which shows the function to return every character from the left or right to the ‘n’ number of delimiter occurrences, respectively.

Have a look at the example of the SUBSTRING_INDEX() function

Using SUBSTRING_INDEX() function with positive occurrences of the delimiter

Let’s take an example

Example 1

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', 1);

Here, we have 1 as a delimiter and 1 for n, so you get every character by the function up to 1 alongwith the delimiter occurrences of 1. You get the following output

Output

+----------------------------------------+
| SUBSTRING_INDEX('Hello World', 'l', 1) |
+----------------------------------------+
| He                                     |
+----------------------------------------+
1 row in set (0.00 sec)

Example 2

Suppose, you write:

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', 3);

Output

+----------------------------------------+
| SUBSTRING_INDEX('Hello World', 'l', 3) |
+----------------------------------------+
| Hello Wor                              |
+----------------------------------------+
1 row in set (0.00 sec)

Using SUBSTRING_INDEX() function with negative occurrences of the delimiter

Example 1

Let’s suppose you code it this way:

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', -1);

Here, the -1 is the n, and 1 is the delimiter, which shows every character by the function from the right to till 1st occurrence of 1 character.

Output

+-----------------------------------------+
| SUBSTRING_INDEX('Hello World', 'l', -1) |
+-----------------------------------------+
| d                                       |
+-----------------------------------------+
1 row in set (0.00 sec)

Example 2

The code you type:

SELECT 
    SUBSTRING_INDEX('Hello World', 'l', - 2) result1,
    SUBSTRING_INDEX('Hello World', 'l', - 3) result2;

Output

+---------+----------+
| result1 | result2  |
+---------+----------+
| o World | lo World |
+---------+----------+
1 row in set (0.00 sec)

Conclusion

In this post, we shed light on how to MYSQL split string by delimiter. Here you get to know about the positive as well as negative occurrences.

I hope enjoyed it! Don’t hesitate to comment in the box below if you need any further assistance.