|
实现步骤
1:创建数据库项目
2:引用相关库,简单实现代码如下
- using System;
- using System.Data;
- using System.Data.SqlClient;
- using System.Data.SqlTypes;
- using Microsoft.SqlServer.Server;
- using System.Text.RegularExpressions;
-
- public partial class RegexSql
- {
- [Microsoft.SqlServer.Server.SqlFunction]
- public static SqlBoolean Regex_IsMatch(string input, string regex)
- {
- return new SqlBoolean(Regex.IsMatch(input, regex, RegexOptions.IgnoreCase));
- }
-
- [Microsoft.SqlServer.Server.SqlFunction]
- public static SqlString Regex_Match(string input, string regex)
- {
- return new SqlString(Regex.Match(input, regex, RegexOptions.IgnoreCase).Value);
- }
- }
3:在MSSQL中添加程序集
http://blog.iyunv.com/ahywg/article/details/39497349
http://blog.iyunv.com/ahywg/article/details/39497349
如果失败请先执行脚本
- exec sp_configure 'show advanced options', '1';
- go
- reconfigure;
- go
- exec sp_configure 'clr enabled', '1'
- go
- reconfigure;
- exec sp_configure 'show advanced options', '1';
-
- go
- ALTER DATABASE [DB_NAME] set TRUSTWORTHY on;
- go
4:创建关联函数
- create function Regex_Match
- ( @input as nvarchar(1000), @regex as nvarchar(100)
- )
- returns nvarchar(1000)
- as
- external name Iftrue_Regex.RegexSql.Regex_Match;
- go
-
- create function Regex_IsMatch
- ( @input as nvarchar(1000), @regex as nvarchar(100)
- )
- returns bit
- as
- external name Iftrue_Regex.RegexSql.Regex_IsMatch;
- go
5:使用函数实现正则匹配- select dbo.Regex_IsMatch('abcde123asdf234','[b-s]+')
- select dbo.Regex_Match('abcde123asdf234','[b-s]+')
注:以上实现是基于framework3.5,在framework4.0下添加程序集会出错,同样方式可以实现很多数据库本身不便实现的功能,如:调用WEB API、WEBSERVICE等
|
|