First, we create a database Project in VSTS, add a class, and implement one of the following methods:
Copy Code code as follows:
<summary>
Regs the ex match.
</summary>
<param name= "inputvalue" >the input value.</param>
<param name= "Regexpattern" >the regex pattern.</param>
<remarks>author:petter Liu http://wintersun.cnblogs.com </remarks>
<returns>1 match,0 not match</returns>
[SqlFunction]
public static bool Regexmatch (string inputvalue, String regexpattern)
{
Any nulls-we can ' t match, return false
if (string. IsNullOrEmpty (inputvalue) | | String. IsNullOrEmpty (Regexpattern))
return false;
Regex r1 = new Regex (Regexpattern.trimend (null));
return R1. Match (Inputvalue.trimend (null)). Success;
}
OK, after build deploy to your target database OK, Visual Studio will automatically register the assembly. If you want to register the assembly manually, you can perform the following T-sql:
Copy Code code as follows:
CREATE ASSEMBLY [regexclr] from ' RegExCLR.dll ';
--Add the REGEX function. We want a friendly name
--Regexmatch rather than the full namespace name.
--the way we have to specify the Assembly.Namespace.Class.Function
--Note the REGEXCLR.REGEXCLR
--(one is the assembly the "is the" namespace)
CREATE FUNCTION Regexmatch (@inputCalue NVARCHAR (4000),
@regexPattern NVARCHAR (4000)) RETURNS BIT
As EXTERNAL NAME RegExCLR.RegExCLR.ClrClass.RegExMatch;
OK, all OK, after we test it:
Select COUNT (1) from Threads where dbo. Regexmatch (ThreadId, ' ^[{|\ (]?[ 0-9a-fa-f]{8}[-]? ([0-9a-fa-f]{4}[-]?) {3} [0-9a-fa-f] {12} [\)|}]? $ ') =1
The T-SQL above is the number of records that find the Threads table ThreadID is a GUID. equals 1 is a match, ^[{|\ (]?[ 0-9a-fa-f]{8}[-]? ([0-9a-fa-f]{4}[-]?) {3} [0-9a-fa-f] {12} [\)|}]? $ regular expression that matches the GUID.
I hope this post will be helpful to you.
You may be interested in the following post:
The performance comparison between the SQLSERVER2008 of CTE and the CLR
SQL Server uses CLR Stored procedure to export data to Excel
