. Advertisement .
..3..
. Advertisement .
..4..
In MySQL, there are numerous built-in functions for performing various operations on string data. It is sometimes necessary MySQL split string by delimiter.
The SUBSTRING INDEX() function splits the string data returned by the query based on a delimiter. This lesson explains how to utilize this function in a SELECT query.
MySQL Split String
We may use MySQL’s substring index() method to divide a string depending on the delimiter. Here’s how to use the function syntax:
string SUBSTRING_INDEX(string, delimiter, count);
The function accepts a text, a delimiter, and a count as parameters. The count will be separated by the specified delimiter, and the number of strings will be returned.
The following is an example of how to use MySQL split string by delimiter:
SELECT SUBSTRING_INDEX('a,b,c,d,e,f', ',', 6);
The following is what the preceding query should return:
a,b,c,d,e,f
Example: MySQL Split String By Delimiter On The Positive Count Value
This tutorial section demonstrates how to utilize the SUBSTRING INDEX() function with a positive count value and various delimiters in four different ways.
Use the SUBSTRING INDEX() function with a positive count value of 1 and a space as the delimiter in the following SELECT statement. There are three words in the main string, ‘Welcome to Disneyland’. As a result, the output will contain the first word of the string.
SELECT SUBSTRING_INDEX('Welcome to Disneyland', ' ', 1);
After running the previous statement, you will get the following output:
mysql> SELECT SUBSTRING_INDEX('Welcome to Disneyland’, ' ', 1);
+---------------------------------------------------------+
| SUBSTRING_INDEX('Welcome to Disneyland', ' ', 1) |
+---------------------------------------------------------+
| Welcome |
+---------------------------------------------------------+
1 row in set (0.01 sec)
Use the positive count number 2 and the character ‘o’ as the delimiter in the following SELECT query, which employs the SUBSTRING INDEX() function.
The character ‘o’ appears twice in the main string, ‘Welcome to Disneyland’. The ‘o’ appears in the second word, ‘to,’ for the second time. ‘Welcome t’ will therefore be the outcome.
SELECT SUBSTRING_INDEX('Welcome to Disneyland', 'to', 3);
After executing the previous statement, the following output will appear:
mysql> SELECT SUBSTRING_INDEX('Welcome to Disneyland', 'to', 3);
+---------------------------------------------------------+
| SUBSTRING_INDEX('Welcome to Disneyland', 'to', 3) |
+---------------------------------------------------------+
| Welcome to Disneyland |
+---------------------------------------------------------+
1 row in set (0.01 sec)
Conclusion
We hope the examples in this tutorial, “MySQL split string by delimiter” will assist MySQL users in understanding how to utilize the SUBSTRING INDEX() function in SELECT queries.
Leave a comment