SQL Server 2005 里使用正则表达式的方法

（手忙脚乱了一通，终于搞定了。记录如下：）

SQL Server 2005的数据库里，CHECK约束不支持正则表达式。造成很大不便。比如我们要判断一个用户名是否符合正确的格式（6~20个由a~z、0~9、_或-组成的字符），如果不用正则表达式，就非常麻烦。

如何让CHECK约束 “支持” 正则表达式呢？微软给出了一个方法：使用CLR——公共语言运行环境。
（微软官方传送门： http://msdn.microsoft.com/zh-cn/magazine/cc163473.aspx ）

（1）首先需要开启CLR功能，否则在最后一步时会出错。
在SQL server 2005 management Studio中，创建查询并执行：

exec sp_configure 'show advanced options', '1';
go
reconfigure;
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
exec sp_configure 'show advanced options', '1';
go    

reconfigure

（2）在VS 2010中创建一个新项目。
创建时选择模版：数据库/SQL Server/Visual C# SQL CLR数据库项目。
VS2010默认的项目目标框架是.NET Framework 4，与SQL server 2005已经不兼容。所以要把目标框架改为.NET Framework 3.5。
如果不改目标框架，在最后一步部署时会出错。
添加数据库引用，选择、连接需要的数据库，如dbTest。

（3）在项目中添加用户自定义函数。
解决方法资源管理器里，项目名称处点右键，选择”添加/用户定义的函数“ 。命名为RegexMatch.cs。
复制粘贴下述代码：
  using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class UserDefinedFunctions

{

    /// <summary>

    /// 是否匹配正则表达式

    /// </summary>

    /// <param name="input">输入的字符串</param>

    /// <param name="pattern">正则表达式</param>

    /// <param name="ignoreCase">是否忽略大小写</param>

    /// <returns></returns>

    [Microsoft.SqlServer.Server.SqlFunction]

    public static bool RegexMatch(string input, string pattern, bool ignoreCase)

    {

        bool isMatch = false;

        if (!string.IsNullOrEmpty(input) && !string.IsNullOrEmpty(pattern))

        {

            try

            {

                Match match = null;

                if (ignoreCase)

                    match = Regex.Match(input, pattern, RegexOptions.Multiline | RegexOptions.IgnoreCase | RegexOptions.Compiled);

                else

                    match = Regex.Match(input, pattern, RegexOptions.Multiline | RegexOptions.Compiled);

                if (match.Success)

                    isMatch = true;

            }

            catch { }

        }

        return isMatch;

    }

    /// 获取正则表达式分组中的字符

    /// </summary>

    /// <param name="input">输入的字符串</param>

    /// <param name="pattern">正则表达式</param>

    /// <param name="groupId">分组的位置</param>

    /// <param name="maxReturnLength">返回字符的最大长度</param>

    /// <returns></returns>

    [Microsoft.SqlServer.Server.SqlFunction]

    public static string GetRegexMatchGroups(string input, string pattern, int groupId, int maxReturnLength)

    {

        string strReturn = string.Empty;

        if (!string.IsNullOrEmpty(input) && !string.IsNullOrEmpty(pattern))

        {

            try

            {

                Match match = Regex.Match(input, pattern, RegexOptions.Multiline | RegexOptions.IgnoreCase | RegexOptions.Compiled);

                if (match.Success && (groupId < match.Groups.Count))

                {

                    strReturn = match.Groups[groupId].Value;

                    strReturn = (strReturn.Length <= maxReturnLength) ? strReturn : strReturn.Substring(0, maxReturnLength);

                }

            }

            catch

            {

                return string.Empty;

            }

        }

        return strReturn;

    }

};

（4）项目名称右键，部署。最后提示：

========== 生成: 成功或最新 1 个，失败 0 个，跳过 0 个 ==========

========== 部署: 成功 1 个，失败 0 个，跳过 0 个 ==========
证明部署成功。 

（5）OK。返回 SQL server 2005 management Studio，在对象资源管理器中，查看dbTest的标量值函数。
可编程性/函数/标量值函数
我们新建的两个函数出现了：dbo.GetRegexMatchGroups 和 dbo.RegexMatch。
两个函数定义在代码里已有解释。

（6）测试一下正则表达式。
SELECT dbo.RegexMatch('guoguiyang_-12312GGGG','^[a-z0-9_-]{6,20}$','true')
返回 0（false）
SELECT dbo.RegexMatch('guoguiyang_-12312GGG','^[a-z0-9_-]{6,20}$','true') 
返回 1（true）
SELECT dbo.RegexMatch('郭大侠_-12312GGGG','^[a-z0-9_-]{6,20}$','true') 
返回 0 （false）

测试成功！可以使用了。 

（7）当然，也可以不在数据库中使用正则表达式，而在数据库管理软件或WEBUI里输入数据时使用正则表达式判断。但个人认为数据库里使用正则表达式匹配可以更加快捷，从根本上杜绝不规范数据的产生。