SqlServer地址提取分离省市区的探索

    工作中需要从订单信息中导入地址并分出省市区，查询了很多资料，多是直接拆分或依赖现成的代码库，而我们在数据库中已有省市区的信息表，如何依赖这个表做文章就比较少资料可参考，为此我们进行了一些探索，最后也基本能提取正确的信息了。

  省市区的表形如上图，使用递归的查询建立视图，形如下图：

FullName是省市区串联起来，FullName2是市区串联起来，RegionIds是把省市区的RegionId串联起来，方便后续查出结果后把RegionId值分配到订单表中的省市区的Id。

然后就是对查询的地址进行处理了，把“省”、“自治区”、“市辖区”等字眼剔除，下图左为原地址，下图右为处理后地址。

最后就是匹配省市区视图的值，用Address like FullName+'%' or like FullName2+'%'，这样只有市区的地址也能匹配到（例子第4行），也能获取到正确的三级Id。另外对于直辖市有不同地址表述的也能匹配到（例子第2、5行）。

省市区视图代码：

WITH regions AS (
	SELECT
		RegionId, 
		RegionName, 
		ParentId, 
		RegionType, 
		CAST(RegionId AS VARCHAR(100)) AS RegionIds,
		CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RegionName, ' ', ''), '省', ''), '市辖区', ''), '壮族自治区', ''), '回族自治区', ''), '维吾尔自治区', ''), '自治区', ''), '特别行政区', '') AS VARCHAR(100)) AS FullName, 
		CAST(CASE WHEN RegionType = 1 THEN '' ELSE RegionName END AS VARCHAR(100)) AS FullName2, 
		1 AS haveChild
	FROM Base_City
	WHERE (ParentId = 1)
	UNION ALL
	SELECT
		a.RegionId,
		a.RegionName, 
		a.ParentId, 
		a.RegionType, 
		CAST(CONVERT(VARCHAR(100), b.RegionIds) + ',' + CONVERT(VARCHAR(100), a.RegionId) AS VARCHAR(100)) AS RegionIds, 
		CAST(b.FullName + a.RegionName AS VARCHAR(100)) AS FullName, CAST(b.FullName2 + a.RegionName AS VARCHAR(100)) AS FullName2,
		(CASE WHEN EXISTS (SELECT 1 FROM Base_City WHERE Base_City.ParentId = a.RegionId) THEN 1 ELSE 0 END) AS haveChild
	FROM Base_City AS a INNER JOIN regions AS b ON a.ParentId = b.RegionId
)
SELECT FullName, FullName2, RegionIds
FROM regions AS v_regions
WHERE (RegionType IN (2, 3)) AND (haveChild = 0)

最后就是把完整地址前面的省市区用FullName和FullName2替换掉，至此提取分离省市区完成。

递归查询可参考

SQL笔记-递归/树形查询_树查询sql-CSDN博客 ​​​​​​