How to Split a String in MySQL Database: MySQL Operators: SUBSTRING_INDEX() Table of Contents Problem: Example: Solution: Discussion: Problem: You’d like to split a string in MySQL. Example: Our database has a table named Student with data in the columns id and name. idname 1Ann Smith 2Mark Twain 3Brad Green Let’s fetch the data from the column name and split it into firstname and lastname. Solution: We’ll use the SUBSTRING_INDEX() function. Here’s the query: SELECT SUBSTRING_INDEX(name,' ',1) AS firstname, SUBSTRING_INDEX(name,' ',-1) AS lastname FROM Student Here’s the result of the query: firstnamelastname AnnSmith MarkTwain BradGreen Discussion: The function SUBSTRING_INDEX() takes 3 arguments: the source string, the delimiter, and the occurrence count of the delimiter. The source string is the string that we would like to split. The delimiter is a string of characters that the SUBSTRING_INDEX() function looks for in the source string. When found, it indicates the place where the substring ends. The delimiter can be a single character such as a space, but it can also be a pattern that consists of two or more characters. This pattern is case sensitive; that is, it matters whether the given characters are upper or lower case. Remember that the delimiter is a string, so it must be written in quotes ('...'). The last argument is the occurrence count. It indicates the number of times the delimiter pattern needs to be matched. Note that the occurrence count can be a negative value. When it is negative, we count the occurrences of the delimiter from the right. When the count is a positive number, we count from the left. The function outputs every character of the source string until the delimiter is matched the number of times given by the occurrence count. If we run the following query: SELECT SUBSTRING_INDEX('Ann Smith', ' ', 1); the output will be: Ann As you can see, the function returned all the characters until the first space from the left was found. In our example, we retrieve as the student’s first name all the characters up to the first space from the left and as the student’s last name all the characters up to the first space from the right. Recommended courses: SQL Basics in MySQL Common MySQL Functions SQL Practice Set in MySQL Recommended articles: MySQL Cheat Sheet 18 Useful Important SQL Functions to Learn ASAP An Overview of SQL Text Functions How to Concatenate Two Columns in SQL – A Detailed Guide SQL String Functions: A Complete Overview See also: How to Trim Strings in SQL How to Replace Part of a String in SQL Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query