如何在无限级分类表中，定位准确的分类项（二：尝试匹配信息）（以城市地域为例）（mssql）

本文链接：https://blog.csdn.net/superwfei/article/details/103184042

我们的目的是创建一个自定义函数，通过给出一些参数，能自动获取到城市的id

Hmmmmm.....，为了减少参数的传递，老顾决定用xml来作为参数传递

例如 dbo.PickCityId '<r a="北京" b="昌平" c="东小口" />'，这样的好处就是，我们可以方便的把数据封装到一个参数里处理，而不用传递多个不确定数量的参数

好了，先给一个具体例子，例如，我们有这样一个数据，名称为“吉林农业大学”，地址为“吉林省长春市新城大街2888号”

那么，第一步，就是把这个数据传递到函数里，不过我们还没建立函数，先进行简单测试，那么就直接创建一个简单的赋值好了

declare @xml xml
set @xml = '<r a="吉林农业大学" b="吉林省长春市新城大街2888号" />'

第一步，我们需要把这个xml解析出来，换算成一个临时表

declare @handle int,@prepare int
exec @prepare = sp_xml_preparedocument @handle output,@xml
;with s as (
		select * from openxml(@handle,'/r',1)
	),s1 as (
		select a.id as rowid,a.localname as col,convert(nvarchar(max),b.text) as val 
		from s a 
		left join s as b on b.parentid=a.id 
		where a.parentid=0
	)
select rowid,val from s1

这样我们就可以得到rowid=2，val=吉林农业大学和rowid=3，val=吉林省长春市新城大街2888号的cte表了

当然，也可以用clr调用正则的方式，由于本文中用到正则的地方不止一处，所以推荐大家在数据库中弄好正则方法，请参考正则表达式使模式匹配和数据提取变得更容易（David Banister）一文，这里有介绍怎么在数据库中使用正则

使用正则的话，用下边的方法获取xml数据

	;with d as (
		select 
		replace(match,'杨凌','杨陵') as addr
		,sn as wsn
		from master.dbo.regexmatches(convert(nvarchar(max),@xml),'(?<=\w+=")[^"]+')
	)
	select * from d

这里给出了一个示例，有些地区的名字各种写法都有，尤其是认为发表的资讯啦、公司地址啦，经常有写错的，在本例中，就是将错误的信息“杨凌”替换为正确的“杨陵”，以对应数据库中的数据，否则，找不到对应的信息？

然后，我们再组织一下地域信息表，用来进行地域匹配

	;with d as (
		select 
		replace(match,'杨凌','杨陵') as addr
		,sn as wsn
		from master.dbo.regexmatches(convert(nvarchar(max),@xml),'(?<=\w+=")[^"]+')
	),t1 as (
		select city_id,city_path,isnull(
			(case 
				when master.dbo.regexismatch(isnull(abbr,''),'[a-z]')=1 
				then city_name else abbr end
			)
			,master.dbo.regexreplace(city_name,'[矿新]区$|自治.*$|[县区]$','')
		) as loc 
		from dbo.dict_cities ci with (nolock) 
		where city_del=0 
		and isnull(targetId,0)=0
	)
	select * from t1

这里同样对地域信息进行了处理，比如说，有简称的取简称，比如有人就把地址写作恩施市xxx路，而不是完整的恩施土家族自治县xxx路，所以，有简称的用简称，再有就是什么矿区啦，新区啦，自治县自治州啦县啦，这些都去掉，否则还是会找不到，比如浦东新区，这是什么鬼？国家级新区？行政区划里没有啊！就按浦东处理吧！再比如，峰峰矿区，嗯这个行政区划里有，但是。。。。人都是懒惰的，谁去写完整的峰峰矿区xxx镇，都是直接写峰峰xxx镇，所以，我们自行处理吧

好了，选择已经出现了两个cte表了，d是我们的参数信息，t1是用来匹配的地域信息，那么，我们就进行第一次匹配吧！

declare @xml xml
set @xml = '<r a="吉林农业大学" b="吉林省长春市新城大街2888号" />'

	;with d as (
		select 
		replace(match,'杨凌','杨陵') as addr
		,sn as wsn
		from master.dbo.regexmatches(convert(nvarchar(max),@xml),'(?<=\w+=")[^"]+')
	),t1 as (
		select city_id,city_path,isnull(
			(case 
				when master.dbo.regexismatch(isnull(abbr,''),'[a-z]')=1 
				then city_name else abbr end
			)
			,master.dbo.regexreplace(city_name,'[矿新]区$|自治.*$|[县区]$','')
		) as loc 
		from dict_cities ci with (nolock) 
		where city_del=0 
		and isnull(targetId,0)=0
	),t2 as (
		select * 
		from d
		cross apply (
			select *,datalength(loc) as mlen 
			from t1
			cross apply (
				select * from master.dbo.RegexMatches(addr,loc)
			) b
			where datalength(loc)>0
		) b
	)
	select * from t2

很好，我们获得了很多行的数据