34,576
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class RegExp
{
// 验证字符串中是否包含与指定的匹配模式一致的字符串
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlBoolean RegExIsMatch(SqlString expression, SqlString pattern)
{
return new SqlBoolean(Regex.IsMatch(expression.ToString(), pattern.ToString()));
}
// 替换字符串中与指定的匹配模式一致的字符串
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString RegExReplace(SqlString expression, SqlString pattern, SqlString replacement)
{
return new SqlString(Regex.Replace(expression.ToString(), pattern.ToString(), replacement.ToString()));
}
// 提取字符串中与指定的匹配模式一致的字符串
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString RegExSubstring(SqlString expression, SqlString pattern, SqlInt32 position, SqlInt32 occurrence)
{
if (expression.ToString().Length < position) return new SqlString("");
if (position <= 0) position = 1;
if (occurrence <= 0) occurrence = 1;
Match m = Regex.Match(expression.ToString().Substring((int) position - 1),pattern.ToString());
for (int i = 1; i < (int)occurrence; i++)
{
m = m.NextMatch();
if (!m.Success) return new SqlString("");
}
return new SqlString(m.ToString());
}
// 计算字符串中与指定的匹配模式一致的字符串的数目
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlInt32 RegExCount(SqlString expression, SqlString pattern, SqlInt32 position)
{
if (expression.ToString().Length < position) return 0;
if (position <= 0) position = 1;
MatchCollection ms = Regex.Matches(expression.ToString().Substring((int)position - 1), pattern.ToString());
return new SqlInt32(ms.Count);
}
// 查找字符串中与指定的匹配模式一致的字符串的位置
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlInt32 RegExIndex(SqlString expression, SqlString pattern, SqlInt32 position, SqlInt32 occurrence)
{
if (expression.ToString().Length < position) return 0;
if (position <= 0) position = 1;
if (occurrence <= 0) occurrence = 1;
Match m = Regex.Match(expression.ToString().Substring((int)position - 1), pattern.ToString());
for (int i = 1; i < (int)occurrence; i++)
{
m = m.NextMatch();
if (!m.Success) return 0;
}
return new SqlInt32(m.Index + 1);
}
}
// 启用 SQL Server 的 CLR 支持
exec sp_configure 'clr enabled',1;
go
reconfigure
go
// 导入程序集
if exists (select * from sys.assemblies where name='RegExp')
drop assembly RegExp;
go
create assembly RegExp authorization dbo
from 'fullpath\ClrRegExClass.dll'
with permission_set=safe;
go
// 创建函数
// @expression 为输入的字符串;@pattern 为正则表达式;
// @position 为字符串开始的位置;@occurrence 为与指定的匹配模式一致的字符串出现的次数
if object_id('dbo.regex_ismatch','FS') is not null
drop function dbo.regex_ismatch;
go
create function dbo.regex_ismatch
(@expression nvarchar(max), @pattern nvarchar(max))
returns bit with returns null on null input
as external name RegExp.RegExp.RegExIsMatch;
go
-- 验证字符串是否以 [server] 开头
-- select dbo.regex_ismatch('[server].[database].[schema].[object]','^\[server\]');
if object_id('dbo.regex_replace','FS') is not null
drop function dbo.regex_replace;
go
create function dbo.regex_replace
(@expression nvarchar(max), @pattern nvarchar(max), @replacement nvarchar(max))
returns nvarchar(max) with returns null on null input
as external name RegExp.RegExp.RegExReplace;
go
-- 将字符串中 [...] 替换为 "..."
-- select dbo.regex_replace('[server].[database].[schema].[object]','\[([\w]*)\]','"$1"');
if object_id('dbo.regex_substring','FS') is not null
drop function dbo.regex_substring;
go
create function dbo.regex_substring
(@expression nvarchar(max), @pattern nvarchar(max), @position int, @occurrence int)
returns nvarchar(max) with returns null on null input
as external name RegExp.RegExp.RegExSubstring;
go
-- 提取字符串中与 [...] 模式匹配的第二次出现的字符串
-- select dbo.regex_substring('[server].[database].[schema].[object]','\[\w*\]',1,2);
if object_id('dbo.regex_count','FS') is not null
drop function dbo.regex_count;
go
create function dbo.regex_count
(@expression nvarchar(max), @pattern nvarchar(max), @position int)
returns int with returns null on null input
as external name RegExp.RegExp.RegExCount;
go
-- 计算字符串中与 [...] 模式匹配的字符串的数目
-- select dbo.regex_count('[server].[database].[schema].[object]','\[\w*\]',1);
if object_id('dbo.regex_index','FS') is not null
drop function dbo.regex_index;
go
create function dbo.regex_index
(@expression nvarchar(max), @pattern nvarchar(max), @position int, @occurrence int)
returns int with returns null on null input
as external name RegExp.RegExp.RegExIndex;
go
-- 查询字符串中与 [...] 模式匹配的第二次出现的字符串开始的位置
-- select dbo.regex_index('[server].[database].[schema].[object]','\[\w*\]',1,2);