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

MySQLMySQLi Database

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)

raja
Updated on 11-Dec-2020 05:35:52

Advertisements