sp_configure 'show advanced options', 1;RECONFIGURE;
sp_configure 'clr enabled', 1;RECONFIGURE;

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text.RegularExpressions;
public partial class UserDefinedFunctions{    [SqlFunction]    public static SqlBoolean RegexMatch(SqlString input, SqlString pattern)    {        if (input.IsNull || pattern.IsNull)            return SqlBoolean.False;
        return Regex.IsMatch(input.Value, pattern.Value);    }}

CREATE ASSEMBLY RegexFunctionsFROM 'C:\Path\To\Your\Compiled\Assembly.dll'WITH PERMISSION_SET = SAFE;

certutil -hashfile SqlLibex.dll SHA512

-- 用正確的格式調(diào)用 sp_add_trusted_assemblyEXEC sp_add_trusted_assembly    0xD8D0A23662B5BEBAC1217EC4DD1F7F2A39A21915C520C0645BE2081AC6E5729E022206CDF7C283721D1B2BE58E74DF63464C8355FBC0823FA486E9C404EC177F,   N'aa';
CREATE ASSEMBLY RegexFunctionsFROM 'D:\BaiduSyncdisk\11Test\SqlLibex\bin\Debug\SqlLibex.dll' WITH permission_set = Safe;

CREATE FUNCTION RegexMatch(@input NVARCHAR(MAX), @pattern NVARCHAR(100))RETURNS BITAS EXTERNAL NAME RegexFunctions.[SqlLibex.UserDefinedFunctions].RegexMatch;

SELECT dbo.RegexMatch('Hello World', '^Hello') AS IsMatch;

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.IO;
public partial class StoredProcedures{    [SqlProcedure]    public static void ReadFileContent(SqlString filePath)    {        if (filePath.IsNull)            return;
        SqlPipe pipe = SqlContext.Pipe;
        try        {            string content = File.ReadAllText(filePath.Value);            SqlDataRecord record = new SqlDataRecord(new SqlMetaData("FileContent", SqlDbType.NVarChar, -1));            pipe.SendResultsStart(record);            record.SetString(0, content);            pipe.SendResultsRow(record);            pipe.SendResultsEnd();        }        catch (Exception ex)        {            pipe.Send(ex.Message);        }    }}

CREATE PROCEDURE ReadFileContent @filePath NVARCHAR(MAX)AS EXTERNAL NAME FileUtilities.StoredProcedures.ReadFileContent;

EXEC ReadFileContent 'C:\Path\To\Your\File.txt';