select
dz,
regexp_replace(dz,regexp_replace(dz,'.*省',''),'') as sheng,
regexp_replace(regexp_replace(dz,'.*省',''),regexp_replace(regexp_replace(dz,'.*省',''),'.*市',''),'') as shi,
if(length(regexp_replace(concat(split(dz,'區')[0],'區'),'.*[省|市]',''))>6,'',regexp_replace(concat(split(dz,'區')[0],'區'),'.*[省|市]','')) as qu,
regexp_replace(regexp_replace(dz,regexp_replace(dz,'.*縣',''),''),'.*[省|市|區]','') xian,
regexp_replace(regexp_replace(dz,regexp_replace(dz,'.*鄉',''),''),'.*[省|市|區|縣]','') xiang,
regexp_replace(regexp_replace(dz,regexp_replace(dz,'.*鎮',''),''),'.*[省|市|區|縣|鄉]','') zhen,
regexp_replace(regexp_replace(dz,regexp_replace(dz,'.*街道',''),''),'.*[省|市|區|縣|鄉]','') jiedao,
regexp_replace(regexp_replace(dz,regexp_replace(dz,'.*村',''),''),'.*[省|市|區|縣|鄉|鎮|街道]','') cun
from test_xzqh