使用给定的示例数据，您可以执行以下操作-
mysql> create table test (marks varchar(100));Query OK, 0 rows affected (0.12 sec)mysql> insert into test values ('Maths-80,Phy-100,Che-99'),('Maths-90,Phy-60'),('Phy-82,Che-65'),('Che-90'),('Maths-33,Phy-89,Che-65'),('Maths-50,Phy-43,Che-59');Query OK, 6 rows affected (0.04 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from test ;+-------------------------+| marks |+-------------------------+| Maths-80,Phy-100,Che-99 || Maths-90,Phy-60 || Phy-82,Che-65|| Che-90 || Maths-33,Phy-89,Che-65 || Maths-50,Phy-43,Che-59 |+-------------------------+6 rows in set (0.00 sec)现在使用
locate，
substring_index我们可以将值提取为
select marks, case when locate('Maths',marks) > 0 then substring_index(substring_index(marks,'Maths-',-1),',',1) else 0 end as Maths ,case when locate('Phy',marks) > 0 then substring_index(substring_index(marks,'Phy-',-1),',',1) else 0 end as Phy ,case when locate('Che',marks) > 0 then substring_index(substring_index(marks,'Che-',-1),',',1) else 0 end as Che from test ;输出 ：
+-------------------------+-------+------+------+| marks | Maths | Phy | Che |+-------------------------+-------+------+------+| Maths-80,Phy-100,Che-99 | 80 | 100 | 99 || Maths-90,Phy-60 | 90 | 60 | 0 || Phy-82,Che-65| 0 | 82 | 65 || Che-90 | 0 | 0 | 90 || Maths-33,Phy-89,Che-65 | 33 | 89 | 65 || Maths-50,Phy-43,Che-59 | 50 | 43 | 59 |+-------------------------+-------+------+------+6 rows in set (0.00 sec)
