SQL Server executes regular expressions, calls C # functions, code

Source: Internet
Author: User



--1. Creating a new Sqlserverext project, writing a C # method to generate a SqlServerExt.dll file
Using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Data.SqlTypes;
Using System.Text.RegularExpressions;
Using Microsoft.SqlServer.Server;


Namespace Ext
{
public static partial class DataBase
{
<summary>
Regular expressions
</summary>
<param name= "Input" > input character </param>
<param name= "pattern" > Regular Expressions </param>
<returns></returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean Regex (SqlChars input, SqlString pattern)
{
Try
{
Regex regex = new Regex (pattern. Value);
return new SqlBoolean (regex. IsMatch (The new string (input. Value));
}
Catch
{
return new SqlBoolean (false);
}
}
}
}


--2. Registering assemblies in SQL Server
CREATE ASSEMBLY UDF
From ' D:\.......\sqlserverext.dll '

with permission_set = SAFE;

--2.1 Deleting a registered assembly UDF
--drop ASSEMBLY Udf;


--3. Creating an SQL function
CREATE FUNCTION Regex
(
@input NVARCHAR (4000),
@pattern nvarchar (4000)
)
RETURNS bit
As
EXTERNAL NAME [UDF]. [Ext.database]. [Regex];
--external Name [assembly name in SQL]. [C # namespace. C # class name]. [C # Method name]

--3.1 Delete a function
--drop FUNCTION Regex;


--4. Test regular
--4.1 Match all numbers
Select Dbo.regex (' 123asd123 ', ' ^\d+$ ');
Select Dbo.regex (' 123000123 ', ' ^\d+$ ');
--4.2 Query the MyCol field in the MyTable table, containing records of all numbers
Select Top Ten * from [MyTable] where Dbo.regex ([MyCol], ' ^\d+$ ');




--5. When executing a custom function exception
--Msg 6263, Level 16, State 1, line 2nd
--Prohibit execution of user code in the. NET Framework. Enable the "CLR enabled" configuration option.
/*
-When prompted below, execute the code below
--Msg 6263, Level 16, State 1, line 2nd
--Prohibit execution of user code in the. NET Framework. Enable the "CLR enabled" configuration option.


exec sp_configure ' show advanced options ', ' 1 ';
Go
Reconfigure
Go
exec sp_configure ' clr enabled ', ' 1 '
Go
Reconfigure
exec sp_configure ' show advanced options ', ' 1 ';
Go
*/

SQL Server executes regular expressions, calls C # functions, code

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.