t-sql 正则表达式
创建程序集 clrregexclass.cs,
并使用 c:windowsmicrosoft.netframeworkv2.0.50727csc.exe /target:library clrregexclass.cs 编译为 clrregexclass.dll 文件。
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());
}
首页 1 2 末页