[MySQL]: How to split a string into multiple columns in a SQL statement

mysql> select * from test;
+——+———————+
| id | name |
+——+———————+
| 1 | /root/path/to/leaf |
| 1 | /var/lib/mysql/data |
+——+———————+
2 rows in set (0.00 sec)

mysql>
mysql> select id,name, SUBSTRING_INDEX(name, ‘/’, 2) as first_val, SUBSTRING_INDEX(name, ‘/’, -1) as last_val from test;
+——+———————+———–+———-+
| id | name | first_val | last_val |
+——+———————+———–+———-+
| 1 | /root/path/to/leaf | /root | leaf |
| 1 | /var/lib/mysql/data | /var | data |
+——+———————+———–+———-+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> select id,name, REPLACE(SUBSTRING_INDEX(name, ‘/’, 2),’/’,”) as first_val, REPLACE(SUBSTRING_INDEX(name, ‘/’, -1),’/’,”) as last_val from test;
+——+———————+———–+———-+
| id | name | first_val | last_val |
+——+———————+———–+———-+
| 1 | /root/path/to/leaf | root | leaf |
| 1 | /var/lib/mysql/data | var | data |
+——+———————+———–+———-+
2 rows in set (0.00 sec)

mysql>
mysql>
mysql> create table new_test (id int(10), name varchar(20), first_val varchar(20), last_val varchar(20));
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> insert into new_test select id,name, REPLACE(SUBSTRING_INDEX(name, ‘/’, 2),’/’,”) as first_val, REPLACE(SUBSTRING_INDEX(name, ‘/’, -1),’/’,”) as last_val from test;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql>
mysql> select * from new_test;
+——+———————+———–+———-+
| id | name | first_val | last_val |
+——+———————+———–+———-+
| 1 | /root/path/to/leaf | root | leaf |
| 1 | /var/lib/mysql/data | var | data |
+——+———————+———–+———-+
2 rows in set (0.00 sec)

mysql>

Any other solutions ?