Use regular expression functions in T-SQL

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.