Use regular expression functions in T-SQL

Have you ever thought about using regular expressions in T-SQL? Yes. We can use SQLSERVERCLRsqlfunction to implement this function.

Have you ever thought about using regular expressions in T-SQL? Yes. We can use SQL server clr SQL function to implement this function.

First, create a Database Project in VSTS and add a class to implement the following method:
The Code is as follows:
///
/// Regs the ex match.
///
/// The input value.
/// The regex pattern.
/// Author: Petter Liu http://wintersun.cnblogs.com
/// 1 match, 0 not match
[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:
The 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: