charindex的应用，这么麻烦

最新推荐文章于 2021-04-15 11:35:46 发布

最新推荐文章于 2021-04-15 11:35:46 发布 · 162 阅读

本文通过一个具体的SQL查询案例，展示了如何使用SQL语句来解析和提取地址信息中的省份、城市及地区等字段。通过对字符串操作函数的巧妙运用，实现了从一个包含层级信息的字符串中准确分离出各级地理单位名称的功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select regplace,replace(substring(isnull(regplace,''),1,charindex('-',isnull(regplace,''))),'-','') provinces,
--substring(isnull(regplace,''),charindex('-',isnull(regplace,'')),charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1)),

replace(stuff(regplace,1,charindex('-',regplace),''),case when charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1)>1 then substring(isnull(regplace,''),charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1),len(isnull(regplace,''))) else '' end,'') citys,
replace(isnull(case when charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1)>1 then substring(isnull(regplace,''),charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1),len(isnull(regplace,''))) else '' end,''),'-','') as areas,
charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1),
case when charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1)>1 then substring(isnull(regplace,''),charindex('-',isnull(regplace,''))+1,(charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1)-len(replace(substring(isnull(regplace,''),1,charindex('-',isnull(regplace,''))),'-','')))) else substring(isnull(regplace,''),1,len(isnull(regplace,''))) end as ends,

case when charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1)>1 then substring(isnull(regplace,''),charindex('-',isnull(regplace,''),charindex('-',isnull(regplace,''))+1),len(isnull(regplace,''))) else '' end
from companyinfo