Mysql截取字段中 用逗号分割 省市县镇街道社区小区
select id , name,
substring_index(name,',',0) as province0,
substring_index(name,',',1) as province1,
substring_index(name,',',2) as province2,
substring_index(name,',',3) as province3,
substring_index(name,',',4) as province4,
substring_index(name,',',5) as province5,
substring_index(name,',',6) as province6,
substring_index(name,',',1) as a1,
SUBSTRING_INDEX(substring_index(name,',',1) , CONCAT(substring_index(name,',',0),',') ,-1) a11,
SUBSTRING_INDEX(substring_index(name,',',2) , CONCAT(substring_index(name,',',1),',') ,-1) a2,
SUBSTRING_INDEX(substring_index(name,',',3) , CONCAT(substring_index(name,',',2),',') ,-1) a3,
SUBSTRING_INDEX(substring_index(name,',',4) , CONCAT(substring_index(name,',',3),',') ,-1) a4,
SUBSTRING_INDEX(substring_index(name,',',5) , CONCAT(substring_index(name,',',4),',') ,-1) a5,
SUBSTRING_INDEX(substring_index(name,',',6) , CONCAT(substring_index(name,',',5),',') ,-1) a6
from test
where SUBSTRING_INDEX(substring_index(name,',',5) , CONCAT(substring_index(name,',',4),',') ,-1) = '社区'
select id , name,
substring_index(name,',',1) as province1,
substring_index(name,',',2) as province2,
substring_index(name,',',3) as province3,
substring_index(name,',',4) as province4,
substring_index(name,',',5) as province5,
substring_index(name,',',6) as province6,
substring_index(name,',',1) as a1,
SUBSTRING_INDEX(substring_index(name,',',2) , CONCAT(substring_index(name,',',1),',') ,-1) a2,
SUBSTRING_INDEX(substring_index(name,',',3) , CONCAT(substring_index(name,',',2),',') ,-1) a3,
SUBSTRING_INDEX(substring_index(name,',',4) , CONCAT(substring_index(name,',',3),',') ,-1) a4,
SUBSTRING_INDEX(substring_index(name,',',5) , CONCAT(substring_index(name,',',4),',') ,-1) a5,
SUBSTRING_INDEX(substring_index(name,',',6) , CONCAT(substring_index(name,',',5),',') ,-1) a6
from test