Mysql截取字段中用逗号分割省市县镇街道社区小区

qq_27327261

已于 2022-08-26 10:39:34 修改

阅读量790

点赞数

分类专栏：博客文章标签： mysql 数据库 sql

于 2022-08-25 10:55:07 首次发布

本文链接：https://blog.csdn.net/qq_27327261/article/details/126520072

版权

博客专栏收录该内容

449 篇文章 6 订阅

订阅专栏

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