SQL Server CLR 极速入门，启用、设计、部署、运行

EXEC sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr

RECONFIGURE WITH OVERRIDE --不加 WITH OVERRIDE在SQL Server 2008 R2上運行不通過

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Function1()
    {
        // 在此处放置代码
        return new SqlString("Hello");
    }
};

INSERT dbo.Articles (txtTitle ,txtContent) SELECT txtTitle , txtContent  FROM dbo.Articles

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean ContainsOne(SqlChars input, string search)
{
    return new string(input.Value).Contains(search);
}

SELECT COUNT(*) FROM dbo.Articles WHERE dbo.ContainsOne(txtContent,'柳永法')=1

using System.Data.SqlTypes;
using System.Text.RegularExpressions;

/*
请先在SQL Server里执行以下命名，来启用CLR
EXEC sp_configure 'clr enabled',1 --1,启用clr 0,禁用clr
RECONFIGURE WITH OVERRIDE
*/
namespace CtripSZ.SQLCLR
{
    public static partial class UserDefinedFunctions
    {


        /// <summary>
        /// SQL CLR 使用正则表达式替换，eg:
        /// select dbo.RegexReplace('<span>柳永法</span>','<.+?>','')
        /// update Articles set txtContent=dbo.RegexReplace(txtContent,'<.+?>','')
        /// --结果：柳永法
        /// </summary>
        /// <param name="input">源串，或字段名</param>
        /// <param name="pattern">正则表达式</param>
        /// <returns>替换后结果</returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString RegexReplace(SqlChars input, SqlString pattern, SqlString replacement)
        {
            if (input.IsNull) return null;
            return Regex.Replace(input.ToStr(), pattern.Value, replacement.Value);
        }

        /// <summary>
        /// SQL CLR 使用正则表达式替换，eg:
        /// select dbo.RegexSearch('<span>柳永法</span>','<.+?>','')
        /// select * from Articles where dbo.RegexIsMatch(txtContent,'柳永法')=1;
        /// </summary>
        /// <param name="input">源串，或字段名</param>
        /// <param name="pattern">正则表达式</param>
        /// <returns>查询结果，1,0</returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlBoolean RegexIsMatch(SqlChars input, string pattern)
        {
            if (input.IsNull) return false;
            return Regex.IsMatch(input.ToStr(), pattern);
        }

        /// <summary>
        /// SQL CLR 使用.net的Contains查找是否满足条件,eg:
        /// select dbo.ContainsOne('我是柳永法，','柳永法');
        /// select * from Articles where dbo.ContainsOne(txtContent,'柳永法')=1;
        /// </summary>
        /// <param name="input">源串，或字段名</param>
        /// <param name="search">要搜索的字符串</param>
        /// <returns>返回是否匹配,1,0</returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlBoolean ContainsOne(SqlChars input, string search)
        {
            if (input.IsNull) return false;
            return input.ToStr().Contains(search);
        }

        /// <summary>
        /// 實現類似 DateTime.ToString("yyyy-MM-dd");
        /// </summary>
        /// <param name="input"></param>
        /// <param name="format"></param>
        /// <returns></returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlString GetDateTimeString(SqlDateTime input, string format)
        {
            if (input.IsNull) return null;
            return input.Value.ToString(format);
        }



        /// <summary>
        /// SQL CLR 使用.net的Contains查找是否满足其中之一的条件,eg:
        /// select dbo.ContainsAny('我是柳永法，','柳|永|法');
        /// select * from Articles where dbo.ContainsAny(txtContent,'柳|永|法')=1;
        /// </summary>
        /// <param name="input">源串，或字段名</param>
        /// <param name="search">要搜索的字符串，以"|"分隔，自己处理空格问题</param>
        /// <returns>返回是否匹配,1,0</returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlBoolean ContainsAny(SqlChars input, string search)
        {
            if (input.IsNull) return false;

            string strTemp = input.ToStr();
            foreach (string item in search.Split('|'))
            {
                if (strTemp.Contains(item))
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// SQL CLR 使用.net的Contains查找是否满足所有的条件,eg:
        /// select dbo.ContainsAll('我是柳永法，','柳|永|法');
        /// select * from Articles where dbo.ContainsAll(txtContent,'柳|永|法')=1;
        /// </summary>
        /// <param name="input">源串，或字段名</param>
        /// <param name="search">要搜索的字符串，以"|"分隔，自己处理空格问题</param>
        /// <returns>返回是否匹配,1,0</returns>
        [Microsoft.SqlServer.Server.SqlFunction]
        public static SqlBoolean ContainsAll(SqlChars input, string search)
        {
            if (input.IsNull) return false;

            string strTemp = input.ToStr();
            foreach (string item in search.Split('|'))
            {
                if (!strTemp.Contains(item))
                {
                    return false;
                }
            }
            return true;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public static string ToStr(this SqlChars input)
        {
            if (input.IsNull) return null;

            return new string(input.Value);
        }
    };

}