正则表达式在处理字符串方面有其特殊的优势,但是 T-SQL 至今没有支持(遗憾)。不过可以通过 CLR 函数“曲线救国”。
闲话不说,开工(需要 SQL Server 2005 及以上版本)。
编写程序集(ClrRegExClass.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 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);
}
}
使用 CSC.EXE 命令直接编译程序集
执行 csc.exe /target:library ClrRegExClass.cs (编译为 ClrRegExClass.dll 文件)。
注意,只要系统中安装了 .NET 2.O,CSC.EXE 就包含在 C:/Windows/Microsoft.NET/Framework/v2.0.50727 目录中 (不需要安装 VS)。
将 ClrRegExClass.dll 导入 SQL Server,并生成相应的 T-SQL 函数
// 启用 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);
完成。
遗憾的是,这些 T-SQL 函数不能像 SQL Server 内置的系统函数一样在任何数据库下直接通过函数名调用。
可选的方法,将这些函数存储在 MASTER 数据库中,再在用户数据库下创建这些函数的同以词以方便调用(有点麻烦)。