Syntax of SUBSTRING_INDEX():
The SUBSTRING_INDEX() function takes three arguments and returns a substring value. The syntax of the SUBSTRING_INDEX() function is provided below:
string SUBSTRING_INDEX(string, delimiter, count);
- The first argument is the string value that will be split.
- The second argument is the delimiter that will be used to split the string value.
- The third argument defines the number of occurrences of the delimiter. It can be positive or negative. If the third argument value is positive, then the substring value will be returned from the left. If the third argument value is negative, the substring value will be returned from the right.
Split String Using SUBSTRING_INDEX() Function:
Different uses of the SUBSTRING_INDEX() function have been shown in this part of this tutorial.
Example 1: Split String Based on the Positive Count Value
This part of the tutorial shows the four uses of the SUBSTRING_INDEX() function with the positive count value and different delimiter.
Run the following SELECT statement that uses the SUBSTRING_INDEX() function with the positive count value, 1, and the space as the delimiter. The main string, ‘Welcome to LinuxHint’, contains three words. So, the first word of the string will be printed in the output.
The following output will appear after executing the previous statement:
Run the following SELECT statement that uses SUBSTRING_INDEX() function with the positive count value, 2 and the character, ‘o’ as the delimiter. The main string, ‘Welcome to LinuxHint’ contains the character, ‘o’ two times. The second time ‘o’ has appeared in the second word, ‘to’. So, the output will be ‘Welcome t’.
The following output will appear after executing the above statement:
Run the following SELECT statement that uses the SUBSTRING_INDEX() function with the positive count value, 1, and the string, ‘to’ as the delimiter. The main string, ‘Welcome to LinuxHint’ contains the string, ‘to’ one time. So, the output will be ‘Welcome’.
The following output will appear after executing the previous statement:
Run the following SELECT statement that uses the SUBSTRING_INDEX() function with the positive count value, 3, and the string, ‘to’, as the delimiter. The main string, ‘Welcome to LinuxHint’ contains the string, ‘to’ one time only. So, the main string will be returned in the output.
The following output will appear after executing the previous statement:
Example 2: Split String Based on the Negative Count Value
This part of the tutorial shows the three uses of the SUBSTRING_INDEX() function with the negative count value and different delimiter have been displayed in this part of the tutorial.
Run the following SELECT statement that uses the SUBSTRING_INDEX() function with the negative count value, -1, and the space as the delimiter. The main string, ‘Welcome to LinuxHint’, contains three words. So, the last word of the string will be printed in the output for the negative value:
The following output will appear after executing the previous statement:
Run the following SELECT statement that uses the SUBSTRING_INDEX() function with the negative count value, -2, and the character, ‘e’ as the delimiter. The main string, ‘Welcome to LinuxHint’, contains the character, ‘e’ one time only. So, the output will be ‘lcome to LinuxHint’:
The following output will appear after executing the previous statement:
Run the following SELECT statement that uses the SUBSTRING_INDEX() function with the negative count value, -2, and the string, ‘in’ as the delimiter. The main string, ‘Welcome to LinuxHint’ contains the string, ‘in’ two times. So, the substring ‘unHint’ will be returned in the output.
The following output will appear after executing the previous statement:
Example 3: Split String Value of the Table
You have to create a table with data in a MySQL database to check the SUBSTRING_INDEX() function for the table data.
Run the following query to create a database named test_db:
Run the following statement to use the test_db database as the current database:
Run the following query to create the customers table with four fields:
id INT NOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
contact_no VARCHAR(15));
Run the following query to insert three records into the customers table:
('001', 'Mahmuda Ferdous', '[email protected]', '+8801928964534'),
('002', 'Zarin Chowdhury', '[email protected]', '+8801855342123'),
('003', 'Mahmudul Hasan', '[email protected]', '+8801728976587');
Run the following command to read all records of the customers table:
The name field contains the first name and last name. The contact_no field contains the mobile number with the country code. The first name and the mobile number without the country code can be read from the table using the SUBSTRING_INDEX() function. In the following SELECT query, the first name will be retrieved by using the space delimiter and 1 as the count value in the SUBSTRING_INDEX() function, and the mobile number without the country code will be retrieved by using the country code as the delimiter and -1 as the count value in the SUBSTRING_INDEX() function:
id, SUBSTRING_INDEX(name,' ',1) AS `First Name`, email, SUBSTRING_INDEX(contact_no,'+88',-1) AS Phone
FROM customers;
The following output will appear after executing the previous statement:
Conclusion:
The different uses of the SUBSTRING_INDEX() function by using different argument values have been discussed in this tutorial by using multiple examples. I hope the examples in this tutorial will help the MySQL users to know the uses of the SUBSTRING_INDEX() function and apply it properly in the SELECT query. Check out other Linux Hint articles for more tips and tutorials.
