CLR创建函数

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

public partial class SqlFunction
{
    //匹配
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString IsMatch(string source,string pattern)
    {
        SqlString Rst;
        if (Regex.IsMatch(source, pattern) == true)
            Rst = "True";
        else
            Rst = "False";

        return Rst;
    }

    //替换
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString MyReplace(string source, string pattern, string value)
    {
        return Regex.Replace(source, pattern, value);
    }

    //分割
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillRow",TableDefinition="ZFCC_C nvarchar(100)")]
    public static IEnumerable Splict(string source, string pattern)
    {
        return Regex.Split(source, pattern);
    }
    public static void FillRow(object obj, out SqlString str)
    {
        str = new SqlString ((string )obj); 
    }
    //统计
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 Occurs(string source, string pattern)
    {
        return Regex.Matches(source, pattern).Count;  
    }

    //截取
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString MySubstr(string source, string pattern)
    {
        string Rst="";
        MatchCollection MCollection;
        MCollection = Regex.Matches(source, pattern);
        foreach (Match  M in MCollection)
            Rst+=M.Value;
        return Rst;
    }

    //排序
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString MySort(string source)
    {
        string Rst="";
        string[] Arr = new string[source.Length];
        for (int i = 0; i < source.Length; i++)
            Arr[i] = source.Substring(i, 1);
        Array.Sort(Arr);
        foreach (string S in Arr)
            Rst += S;

        return Rst;
    }
};

 

--Sql Server 2005

Create Assembly CLRFunction From 'F:\Mxbing1984\C#\CLR\CLRFunction.Dll' 
With Permission_Set=External_Access
GO
Create Function IsMatch(@Source nvarchar(100),@Pattern nVarchar(10))
Returns
As
External Name CLRFunction.CLRFunction.IsMatch
GO