Sql Server使用Regex

近日因項目需求，需要在sql server中用到Regex，因Sql Server本身並不支援Regex，需要用到Clr函數。

在此記錄一下步驟，與大家共用，雖然寫的是原創，但有參考網上的文章。

1.使用 VS2010建立一個新的項目，選擇【Visual C#  SQL CLR資料庫專案】，輸入項目名稱，我這裡為：SqlClrFunctions

注意：因我的是sql server 2008 R2，所以選擇.Net Framework 3.5（這一點很重要）

2.給項目添加一個類，我取名為：SqlClrFunctions.cs

3.在彈出的對話方塊輸入類開：SqlClrFunctions.cs

4.輸入以下C#代碼：

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlTypes;using System.Text.RegularExpressions;namespace ClrFunctions{    public static class SqlClrFunctions    {        static SqlClrFunctions() { }                      [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]        public static SqlBoolean RegexLike(SqlString input, SqlString pattern, SqlInt32 options)        {            if (input.IsNull || pattern.IsNull)            {                return new SqlBoolean(false);            }            bool result = Regex.IsMatch(input.Value, pattern.Value, (RegexOptions)options.Value);            return new SqlBoolean(result);        }        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]        public static SqlString RegexReplace(SqlString input, SqlString pattern, SqlString replacement, SqlInt32 options)        {            if (input.IsNull || pattern.IsNull || replacement.IsNull)            {                return input;            }            string s = Regex.Replace(input.Value, pattern.Value, replacement.Value, (RegexOptions)options.Value);            return new SqlString(s);        }        //    RegexOptions.None, 0;        //    RegexOptions.IgnoreCase, 1;        //    RegexOptions.Multiline, 2;        //    RegexOptions.ExplicitCapture, 4;        //    RegexOptions.Compiled, 8;        //    RegexOptions.Singleline, 16;        //    RegexOptions.IgnorePatternWhitespace, 32;        //    RegexOptions.RightToLeft, 64;        //    RegexOptions.ECMAScript, 256;        //    RegexOptions.CultureInvariant, 512;        //    (RegexOptions.IgnoreCase | RegexOptions.Multiline), 3;    }}

5.點擊產生，或者直接按快速鍵Ctrl+Shift+B

6.執行以下sql指令碼，允許執行clr函數

sp_configure 'show advanced options', 1;GORECONFIGURE WITH OVERRIDE;GOsp_configure 'clr enabled',1 ;GORECONFIGURE WITH OVERRIDE;GO

7.執行sql指令碼，此處的dll為實際dll的完整路徑

CREATE ASSEMBLY [SqlClrFunctions]Authorization [dbo]FROM 'D:\users\mark\documents\visual studio 2010\Projects\SqlClrFunctions\SqlClrFunctions\bin\Debug\SqlClrFunctions.dll' WITH PERMISSION_SET = SAFE

8.執行sql指令碼建立函數：

CREATE FUNCTION dbo.fn_RegexReplace(@input nvarchar(4000),@pattern nvarchar(1000),@replacement nvarchar(1000),@options INT ) RETURNS nvarchar(4000)  AS EXTERNAL NAME SqlClrFunctions.[ClrFunctions.SqlClrFunctions].RegexReplace; 

9.執行sql測試：

SELECT dbo.fn_RegexReplace('ABC)DEF.ABCDEF (ABCD GGGG ABC 900 ABC','(?i)(?<=^|[\s()])ABC(?=[\W]|$)','123',0);

10.看看結果：

續：

如果我們要發布指令碼的時候，直接用DLL可能就不太方便，有沒有更好的方法呢？當然有，請看：

1.在SSMS（sql server管理器）裡面依次展開資料庫名-->可程式化性-->程式集-->第7步的程式集名稱

2.這裡我們就可以看到以下sql指令碼：

USE [Test]GO/****** Object:  SqlAssembly [SqlClrFunctions]    Script Date: 08/18/2013 22:50:06 ******/CREATE ASSEMBLY [SqlClrFunctions]AUTHORIZATION [dbo]FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030095DD10520000000000000000E00002210B010B00000A00000006000000000000FE280000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B02800004B000000004000006803000000000000000000000000000000000000006000000C000000082800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000000409000000200000000A000000020000000000000000000000000000200000602E72737263000000680300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000E028000000000000480000000200050010210000F806000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000A002A00133003004B00000001000011000F00280F00000A2D0C0F01280F00000A16FE012B01160C082D0A0016731000000A0B2B240F00281100000A0F01281100000A0F02281200000A281300000A0A06731000000A0B2B00072A00133004005600000002000011000F00280F00000A2D150F01280F00000A2D0C0F02280F00000A16FE012B01160C082D0500020B2B2B0F00281100000A0F01281100000A0F02281100000A0F03281200000A281400000A0A06731500000A0B2B00072A000042534A4201000100000000000C00000076322E302E35303732370000000005006C0000005C020000237E0000C80200002403000023537472696E677300000000EC0500000800000023555300F405000010000000234755494400000004060000F400000023426C6F620000000000000002000001471502000900000000FA2533001600000100000015000000020000000300000007000000150000000D00000002000000010000000300000000000A0001000000000006004B0044000A007A0065000A00850065000A008F0065000600E300D10006000001D10006001D01D10006003C01D10006005501D10006006E01D10006008901D1000600A401D1000600DC01BD010600F001D10006001C0209023F003002000006005F023F0206007F023F020A00B8029D020E000103E2020E000703E2020000000001000000000001000100810100001E002E00050001000100502000000000911852000A000100542000000000960098000E000100AC20000000009600A2001900040000000100AF0000000200B50000000300BD0000000100AF0000000200B50000000300C50000000400BD002900FA0026003100FA0026003900FA0026004100FA0026004900FA0026005100FA0026005900FA0026006100FA0026006900FA002B007100FA0026007900FA0030008900FA0036009100FA003B009900FA003B001900CD0264001100FA002B001900D80268002100D8026C00A10014037000A1001C037F001900FA0026002E002B008F002E006B00D4002E000B008F002E001300A4002E001B00A4002E002300A4002E003300AA002E003B00A4002E004B00A4002E005B00C2002E006300CB00400073003F00600073003F007800880004800000010000007213CA9F0000000000001E00000002000000000000000000000001003B0000000000020000000000000000000000010059000000000002000000000000000000000001004400000000000000003C4D6F64756C653E0053716C436C7246756E6374696F6E732E646C6C0053716C436C7246756E6374696F6E7300436C7246756E6374696F6E73006D73636F726C69620053797374656D004F626A656374002E6363746F720053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670053716C496E7433320052656765784C696B650052656765785265706C61636500696E707574007061747465726E006F7074696F6E73007265706C6163656D656E740053797374656D2E5265666C656374696F6E00417373656D626C795469746C65417474726962757465002E63746F7200417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780052656765784F7074696F6E730049734D61746368005265706C616365000003200000000000FC9F56A0249CA844BA8F0B205D51E38B0008B77A5C561934E089030000010A00031109110D110D11110C0004110D110D110D110D1111042001010E0420010102052001011141042001010803200001240100020054020F497344657465726D696E69737469630154020949735072656369736501032000020320000E03200008070003020E0E1155060703021109020800040E0E0E0E11550607030E110D021401000F53716C436C7246756E6374696F6E73000005010000000017010012436F7079726967687420C2A920203230313300000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000095DD105200000000020000008A00000024280000240A00005253445352F1CA3693EC6A42ACFC684F1C57210F09000000643A5C75736572735C6D61726B5C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C53716C436C7246756E6374696F6E735C53716C436C7246756E6374696F6E735C6F626A5C44656275675C53716C436C7246756E6374696F6E732E706462000000D82800000000000000000000EE280000002000000000000000000000000000000000000000000000E02800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000100300000000000000000000100334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100CA9F721300000100CA9F72133F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00470020000010053007400720069006E006700460069006C00650049006E0066006F0000004C0200000100300030003000300030003400620030000000480010000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0043006C007200460075006E006300740069006F006E007300000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003900370038002E00340030003900300036000000000048001400010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C007200460075006E006300740069006F006E0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100330000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C007200460075006E006300740069006F006E0073002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D00650000000000530071006C0043006C007200460075006E006300740069006F006E007300000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003900370038002E00340030003900300036000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003900370038002E0034003000390030003600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000003900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000WITH PERMISSION_SET = SAFEGO

3.這樣我們就可以直接用sql指令碼，不需要dll了。