SQL Server uses regular expressions

Recently, due to project requirements, regular expressions need to be used in SQL Server. Because SQL server itself does not support regular expressions, CLR functions are required.

Record the steps here and share them with you. Although it is original, you can refer to the online articles.

1. Use vs2010 to create a new project, select Visual C # SQL clr database project, and enter the project name. Here I am: sqlclrfunctions

Note: Because SQL Server 2008 R2 is used, select. NET Framework 3.5 (this is important)

2. Add a class to the project. I named it sqlclrfunctions. CS.

3. In the pop-up dialog box, enter sqlclrfunctions. CS.

4. Enter the following C # code:

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. Click Generate or press the shortcut key Ctrl + Shift + B.

6. Execute the following SQL script to allow the execution of CLR Functions

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

7. Execute the SQL script. The DLL here is the complete path of the actual 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. Execute an SQL script to create a function:

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. Run the SQL test:

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

10. Check the result:

Continued:

If we want to release the script, it may be inconvenient to directly use the DLL. Is there a better way? Of course, see:

1. In SSMs (SQL Server Manager), expand the database name> programmable> Assembly name in step 1.

2. Here we can see the following SQL script:

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. In this way, we can directly use SQL scripts without the need for DLL.