嗯,历史遗留问题,在网站编码未统一之前,部分数据中,字符的格式不是标准的unicode字符,而是类似天这样的符号定义,但这种定义,我们显示在网站里没什么问题,但用来检索、统计等时候,就会出现问题了,所以我们需要把他转成标准的unicode字符
在sqlserver里,我们可以直接将数字转成字符,例如 char(32)就是空格,而超过8位长度的字符,我们需要用nchar来转换,比如nchar(22825)就是汉字“天”
那么就很简单了,我们只需要在正文中检索所有符合&#数字;这种格式的定义,然后转成unicode字符即可
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[UniCodeToNChar]
(
@str nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @result nvarchar(max),@unicode varchar(10),@c nvarchar(10)
set @result = @str
declare cur cursor local for
select distinct match,nchar(convert(int,master.dbo.regexmatch(match,'\d+'))) as c from master.dbo.RegexMatches(@str,'&#\d+;')
open cur
fetch next from cur into @unicode,@c
while @@fetch_status=0
begin
set @result = replace(@result,@unicode,@c)
fetch next from cur into @unicode,@c
end
close cur
deallocate cur
RETURN @result
END
在这个函数里,我使用了以前我所定义的一个clr函数,正则相关的内容,用来筛选字符格式
有兴趣的可以参考转载 正则表达式使模式匹配和数据提取变得更容易(David Banister)这个文章,来自己实现
或者下载我的资源“mssql正则clr及函数.txt”直接运行里边的指令,自动创建相关函数
在将生成 clr 对应的 dll 导入到数据库的时候,还需要注意 framework 的版本,老顾自己的这个资源,是 framework 4.0 版本的,有些可能需要 2.0 或 3.5 版本的,那就只能看转载的这个文章,自己用 c# 重新抄一遍然后导出了。
嗯,因为我是自己配置的数据库,具有master操作权限,所以我把程序集和函数扔到master里了,可自行调整函数所在数据库
然后就是unicode转换函数的调用了,这个就没什么说的了
select master.dbo.UniCodeToNChar('测试天津市东丽区教育局机关,以及天津市教委')