Split a column in 2 columns using comma as separator - MySQL

For this, you can use substring_index() in MySQL. Let us create a table −

Example

mysql> create table demo79
   -> (
   -> fullname varchar(50)
   -> );
Query OK, 0 rows affected (0.64

Insert some records into the table with the help of insert command −

Example

mysql> insert into demo79 values("John,Smith");
Query OK, 1 row affected (0.09

mysql> insert into demo79 values("David,Miller");
Query OK, 1 row affected (0.11

mysql> insert into demo79 values("Chris,Brown");
Query OK, 1 row affected (0.07

Display records from the table using select statement −

Example

mysql> select *from demo79;

This will produce the following output −

Output

+--------------+
| fullname     |
+--------------+
| John,Smith   |
| David,Miller |
| Chris,Brown  |
+--------------+
3 rows in set (0.00 sec)

Following is the query to split a column in 2 columns using comma as separator −

Example

mysql> select
   -> fullname,
   -> substring_index(fullname, ',', 1) First_Name,
   -> substring_index(fullname, ',', -1) Last_Name
   -> from demo79;

This will produce the following output −

Output

| fullname     | First_Name | Last_Name |
+--------------+------------+-----------+
| John,Smith   | John       | Smith     |
| David,Miller | David      | Miller    |
| Chris,Brown  | Chris      | Brown     |
+--------------+------------+-----------+
3 rows in set (0.00 sec)