本文共 2704 字，大约阅读时间需要 9 分钟。

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

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

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

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

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

declare @xml xmlset @xml = '
   '

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

declare @handle int,@prepare intexec @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调用正则的方式，由于本文中用到正则的地方不止一处，所以推荐大家在数据库中弄好正则方法，请参考一文，这里有介绍怎么在数据库中使用正则

使用正则的话，用下边的方法获取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 xmlset @xml = '
   '	;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

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

各列的意义为addr为匹配数据，wsm为第几个匹配项，city_id、city_path、loc不解释，sn为匹配序号，match为匹配内容,index为匹配结果所在位置，mlen为匹配结果字符串长度

很明显，这些数据需要处理后才能得到我们的最终结果长春

转载地址：http://mxvxi.baihongyu.com/