--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>
The regular form of the expression
</summary>
<param name= "Input" > input character </param>
<param name= "pattern" > Regular form </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. Register 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 mytable table in MyCol field, including all numbers of records
Select Top Ten * from [MyTable] where Dbo.regex ([MyCol], ' ^\d+$ ');
--5. When you run your own definition function exception
--Message 6263. Level 16, State 1, line 2nd
--Prohibit running user code in the. NET Framework. Enable the "CLR enabled" configuration option.
/*
--run the code below when prompted for example
--Msg 6263, Level 16, State 1, line 2nd
--Prohibit running 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 runs the normal table, calling C # functions, code