Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use regular expression functions in T-SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

First, we create a Database Project in VSTS, add a class, and 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;}

All right, after Build, Deploy to your Target database will OK, and VisualStudio will automatically register the assembly. If you want to register the assembly manually, 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 all 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 is the number of records that find out that Threads table ThreadId is GUID. Equal to 1 is a match, ^ [{|\ (]? [0-9a-fA-F] {8} [-]? ([0-9a-fA-F] {4} [-]?) {3} [0-9a-fA-F] {12} [\) |}]? $matches the regular expression of GUID. That's it. I hope this POST article is helpful to you. You may be interested in the following POST: performance comparison between Split and CLR for CTE in SQLSERVER2008 SQLSERVER uses CLR Stored Procedure to export data to Excel

After reading the above, do you have any further understanding of how to use regular expression functions in T-SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.