Use regular expression functions in T-SQL

First, create a Database Project in VSTS and add a class to implement the following method:
Copy codeThe Code is 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;
}

Okay. After the Build, Deploy will be OK to your Target database, and Visual Studio will automatically register this assembly. If you want to manually register an assembly, you can execute the following T-SQL:
Copy codeThe Code is as follows:
Create assembly [RegExCLR] FROM 'regexclr. dll ';

-- Add the REGEX function. We want a friendly name
-- RegExMatch rather than the full namespace name.
-- Note the way we have to specify the Assembly. Namespace. Class. Function
-- NOTE the RegExCLR. RegExCLR
-- (One is the assembly the other is the namespace)
Create function RegExMatch (@ inputCalue NVARCHAR (4000 ),
@ RegexPattern NVARCHAR (4000) RETURNS BIT
As external name RegExCLR. RegExCLR. ClrClass. RegExMatch;

OK, after everything is OK, let's test:

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 above T-SQL is to find out the number of records where ThreadId is GUID in the Threads table. 1 is a match, ^ [{| \ (]? [0-9a-fA-F] {8} [-]? ([0-9a-fA-F] {4} [-]?) {3} [0-9a-fA-F] {12} [\) |}]? $ The regular expression that matches the GUID.

I hope this POST will help you.

You may be interested in the following POST:

Comparison of the Split and CLR performance of CTE in SQLSERVER2008

SQLSERVER use CLR Stored Procedure to export data to Excel