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

--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