Often while using MySQL, you may need to split a column into two. There is no direct MySQL function that allows you to split strings. In this article, we will learn how to split field into two in MySQL.
How to Split Field Into Two in MySQL
Let us say you have the following table students(id, name) where name column contains first and last name information in a single column.
mysql> create table students(id int, name varchar(20)); mysql> insert into students(id, name) values(1, 'John Doe'),(2, 'Jinny Doe'),(3,'Jack Doe'), (4,'Jane Doe'); mysql> select * from students; 1, John Doe 2, Jinny Doe 3, Jack Doe 4, Jane Doe
Let us say you want to split the value in name column, into two separate columns, first_name and last_name.
You can easily do this using substring_index() function.
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 1), ' ', -1) as first_name,
SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', 2), ' ', -1) as last_name
FROM students;
John, Doe
Jinny, Doe
Jack, Doe
Jane Doe
substring_index() function returns substring of a string before a specific number of delimiter occurs. Here is its syntax.
SUBSTRING_INDEX(string, delimiter, number)
The first column is the input string, second one is the delimiter to search for, and the last argument is the number of delimiter. It can be both positive and negative value. If it is positive then function returns substring to left of delimiter. If it is negative, function returns substring to right of delimiter.
In our query above, we specify the first argument of substring_index as column to be split. The second argument is the space delimiter and the third argument is 1 to extract first name and -1 to extract last name.
In this article, we have learnt how to split field into two columns in MySQL.
Also read:
How to Fix Can’t Connect to Local MySQL Socket Error
How to Make Case Sensitive String Comparison in MySQL
How to Fix MySQL Shutdown Unexpectedly Error
How to Fix 2006 MySQL Server Has Gone Away
How to Get Previous URL in JavaScript