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

柔光的暖阳◎ 2024-04-08 12:04 55阅读 0赞

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)   = '社区'

![5d7a395865e745b7b76d04ba0b2f05ca.png][]

![7a999fd1347c4105ae9a6d06a7c26eba.png][]

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

![7057b1f961084da78e154359bba3b6fb.png][]

[5d7a395865e745b7b76d04ba0b2f05ca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/bd4598c3deca434d8b66cab058ca10e1.png
[7a999fd1347c4105ae9a6d06a7c26eba.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/c05f17dc807e46d1a565bc20fd374324.png
[7057b1f961084da78e154359bba3b6fb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/383f956a0e5640208f9372a071862b00.png