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