在 Sql Server 中实现 UrlDecode

最近在公司网站中要做一个统计，统计一下按关键字或者来源网站关键字的数量，结果发现数据库中太多的 URL 地址中出现了汉字，并且，这些汉字还是经过了 UrlEncode 之后的内容，天啊，你玩死文盲吧，难道每统计一次，你都想让文盲把这些内容用程序做下解码吗？

于是，文盲同学发扬了共享精神，赶紧上网搜怎么用 SqlServer 进行 UrlDecode。。。。。时间过去很久（两个小时）。。。。没有相关的结果。。。即便是有相关内容，也都是使用System.Web.HttpUtility.UrlDecode进行实现的，但问题是用这个方法实现的话，CLR还需要 System.Web.dll以及其所依赖的其他组件支持，实在是太麻烦了

再然后，实在没有相关内容了，只好自己写一个CLR程序来进行支持了（其实到此为止都是废话，文盲实在想不明白，为什么网上搜不到相关内容）

----------------------------------------------------------------------------------------------------

首先，我们需要实现不使用System.Web.HttpUtility.UrlDecode这个方法的解码方式

于是先写一个解码函数，这个函数需要录入一个字符串，如果有编码后的内容，将编码后的内容解码，否则正常返回

所以先做一个支持函数

        private static byte[] EnCodeToChar(Match match)
        {
            if (Regex.IsMatch(match.Value, @"%[0-9a-f]{2}", RegexOptions.IgnoreCase))
            {
                return new byte[] { (byte)Convert.ToInt32(match.Value.Replace("%", ""), 16) };
            }
            else
            {
                return Encoding.UTF8.GetBytes(match.Value);
            }
        }

这个函数的输入参数是正则匹配结果，返回的则是对应的 byte[]，为什么这么写呢，因为不管是什么编码类型，编码内容都符合 %00-%FF这个规则，即：百分号后跟随两位十六进制数字，先不管它是utf（三位）还是gb2312（两位），至少转成 byte[] 是没错的

然后将对应字符串的byte[]集合按顺序合并成一个大byte[]，然后就可以直接使用 Encoding.GetString()方法来转成字符串了，编码在这一步进行实现

那么分解字符串并合并byte[]的支持函数也是需要的

        private static byte[] UTF8Byte(string str)
        {
            MatchCollection mc = Regex.Matches(str, @"%[0-9a-f]{2}|[\s\S]", RegexOptions.IgnoreCase);
            List<byte[]> btlist = new List<byte[]>();
            int s = 0;
            for (int i = 0; i < mc.Count; i++)
            {
                byte[] t = EnCodeToChar(mc[i]);
                btlist.Add(t);
                s += t.Length;
            }
            byte[] bt = new byte[s];
            s = 0;
            for (int i = 0; i < btlist.Count; i++)
            {
                for (int j = 0; j < btlist[i].Length; j++)
                {
                    bt.SetValue(((byte[])btlist[i])[j], s);
                    s++;
                }
            }
            return bt;
        }

最后，为了能够符合CLR程序集要求，并在SqlServer中使用，写一个SqlFunction

    [SqlFunction]
    public static SqlString UTF8Decode(SqlChars input)
    {
        return Encoding.UTF8.GetString(UTF8Byte(new string(input.Value)));
    }

然后把程序集导入到数据库，并建立自定义函数

CREATE FUNCTION [dbo].[UTF8Decode](@expression [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UrlDecode].[UrlDecode].[UTF8Decode]

恩恩恩，测试了一下，非常不错，可以直接在数据库中使用了

----------------------------------------------------------------------------------------------------

其实这么看来，UrlDecode和UrlEncode也没有那么神秘了，其实算法还是很简单的，那么，就来最后一步吧

考虑到不是所有网站的编码都是一致的，上述内容只实现了UTF8编码下的解码，现在做一个支持任意编码的解码方法吧

以下是完整代码

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Text;
using System.Text.RegularExpressions;

public static partial class UrlDecode
{
    [SqlFunction]
    public static SqlString UTF8Decode(SqlChars input)
    {
        return Encoding.UTF8.GetString(UTF8Byte(new string(input.Value)));
    }
    [SqlFunction]
    public static SqlString Decode(SqlChars input, SqlString charset)
    {
        Encoding en = Encoding.UTF8;
        try
        {
            en = Encoding.GetEncoding(charset.Value);
        }
        catch (Exception ex)
        {
        }
        return en.GetString(GetCharsetBytes(new string(input.Value), en));
    }

    private static byte[] EnCodeToChar(Match match, Encoding en)
    {
        if (Regex.IsMatch(match.Value, @"%[0-9a-f]{2}", RegexOptions.IgnoreCase))
        {
            return new byte[] { (byte)Convert.ToInt32(match.Value.Replace("%", ""), 16) };
        }
        else
        {
            return en.GetBytes(match.Value);
        }
    }
    private static byte[] GetCharsetBytes(string str, Encoding en)
    {
        MatchCollection mc = Regex.Matches(str, @"%[0-9a-f]{2}|[\s\S]", RegexOptions.IgnoreCase);
        List<byte[]> btlist = new List<byte[]>();
        int s = 0;
        for (int i = 0; i < mc.Count; i++)
        {
            byte[] t = EnCodeToChar(mc[i], en);
            btlist.Add(t);
            s += t.Length;
        }
        byte[] bt = new byte[s];
        s = 0;
        for (int i = 0; i < btlist.Count; i++)
        {
            for (int j = 0; j < btlist[i].Length; j++)
            {
                bt.SetValue(((byte[])btlist[i])[j], s);
                s++;
            }
        }
        return bt;
    }

    private static byte[] EnCodeToChar(Match match)
    {
        if (Regex.IsMatch(match.Value, @"%[0-9a-f]{2}", RegexOptions.IgnoreCase))
        {
            return new byte[] { (byte)Convert.ToInt32(match.Value.Replace("%", ""), 16) };
        }
        else
        {
            return Encoding.UTF8.GetBytes(match.Value);
        }
    }
    private static byte[] UTF8Byte(string str)
    {
        MatchCollection mc = Regex.Matches(str, @"%[0-9a-f]{2}|[\s\S]", RegexOptions.IgnoreCase);
        List<byte[]> btlist = new List<byte[]>();
        int s = 0;
        for (int i = 0; i < mc.Count; i++)
        {
            byte[] t = EnCodeToChar(mc[i]);
            btlist.Add(t);
            s += t.Length;
        }
        byte[] bt = new byte[s];
        s = 0;
        for (int i = 0; i < btlist.Count; i++)
        {
            for (int j = 0; j < btlist[i].Length; j++)
            {
                bt.SetValue(((byte[])btlist[i])[j], s);
                s++;
            }
        }
        return bt;
    }
}

CREATE FUNCTION [dbo].[UTF8Decode](@expression [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UrlDecode].[UrlDecode].[UTF8Decode]

CREATE FUNCTION [dbo].[UrlDecode](@expression [nvarchar](max), @pattern [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UrlDecode].[UrlDecode].[Decode]

select *,count(0) as cnt,dbo.UTF8Decode(keyword) from tableName order by cnt desc

perfect，就这么完成了，支持任意编码的方法则使用

select *,count(0) as cnt,dbo.UrlDecode(keyword,'gb2312') from tableName order by cnt desc

需要注意的是，如果输入的编码名称无法解析，则自动使用utf8进行解码了

以上，所有内容就都完成了，当然，如果在添加程序集出错了，请参考文盲的另一篇博文参考下基本就可以解决了哦

好吧。。。写完了这个文章，才发现，原来有其他方式实现，可以不使用 CLR 方法，真的是玩死文盲了

http://www.cnblogs.com/guanjie20/p/3412446.html

http://blog.csdn.net/ruijc/article/details/6931189