SqlQuantumLeap

SQLCLR UDA for Longest Common Substring - Installer

Mar 14th, 2016
265
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.     This script relates to the following DBA.StackExchange answer:
  3.     http://dba.stackexchange.com/questions/131759/is-there-a-sql-server-implementation-of-the-longest-common-substring-problem/131766#131766
  4.  
  5.     The Assembly below is compiled against .NET Framework version 3.5 so that it will work in SQL Server 2008 and
  6.     newer (i.e. all versions that support SQLCLR outside of SQL Server 2005). This does not work on SQL Server
  7.     2005 due to using more than a single input parameter for the UDA.
  8.  
  9.     The source code for the following User-Defined Aggregate (UDA) is located at:
  10.     http://pastebin.com/ceDypBp0
  11.  
  12.     Date: 2016-03-16
  13.     Version: 1.0.3
  14.  
  15.     For more functions like this, please visit: http://SQLsharp.com
  16.  
  17.     Copyright (c) 2016 Sql Quantum Leap. All rights reserved.
  18.     http://www.SqlQuantumLeap.com
  19. */
  20.  
  21.  
  22.  
  23. /* -- CLEAN UP (if needed)
  24. USE [ {your_database_name} ];
  25. DROP AGGREGATE [dbo].[LongestCommonSubstring];
  26. DROP ASSEMBLY [SqlQuantumLeap.LongestCommonSubstring];
  27. */
  28.  
  29. SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
  30.  
  31. -------------------------------------------------------------------------------------
  32. --                   ENABLE "CLR Integration" (ONLY IF NOT ALREADY ENABLED)
  33. -------------------------------------------------------------------------------------
  34. IF EXISTS(
  35.             SELECT *
  36.             FROM   sys.configurations sc
  37.             WHERE  sc.[name] = N'clr enabled'
  38.             AND    sc.[value_in_use] = 0
  39.         )
  40. BEGIN
  41.     PRINT 'Enabling "CLR Integration"...';
  42.     EXEC sp_configure N'clr enabled', 1;
  43.     RECONFIGURE;
  44. END;
  45. -------------------------------------------------------------------------------------
  46.  
  47. USE [ {your_database_name} ];
  48. GO
  49. PRINT N'Creating [SqlQuantumLeap.LongestCommonSubstring] Assembly...';
  50. GO
  51. CREATE ASSEMBLY [SqlQuantumLeap.LongestCommonSubstring]
  52.     AUTHORIZATION [dbo]
  53.     FROM 0x\
  54. 4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1F\
  55. BA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A240000000000000050450000\
  56. 4C0103006604EA560000000000000000E00002210B010B00001800000008000000000000FE3600000020000000400000000000100020000000020000040000000000\
  57. 00000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000B03600004B000000\
  58. 004000006004000000000000000000000000000000000000006000000C000000783500001C0000000000000000000000000000000000000000000000000000000000\
  59. 000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000041700000020000000180000\
  60. 00020000000000000000000000000000200000602E72737263000000600400000040000000060000001A0000000000000000000000000000400000402E72656C6F63\
  61. 00000C0000000060000000020000002000000000000000000000000000004000004200000000000000000000000000000000E0360000000000004800000002000500\
  62. 54270000240E00000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E202\
  63. 167D01000004027E1200000A7D0200000402177D0300000402731300000A7D0400000402167D05000004027C06000004FE150200001B2A00000003300300F1000000\
  64. 00000000027C06000004281400000A2D230F02281500000A2C0E0217731600000A7D060000042B0C0216731600000A7D060000040F01281700000A2D08027B010000\
  65. 042C012A0F01281800000A6F1900000A7E1200000A281A00000A2C1E02177D01000004027B040000046F1B00000A027E1200000A7D020000042A027B030000042C4F\
  66. 027B020000047E1200000A281A00000A2C0E020F01281800000A7D020000042A020F01281800000A027B0200000428040000062D0702177D01000004027E1200000A\
  67. 7D0200000402167D030000042A020F01281800000A2803000006027B040000046F1C00000A2D0702177D010000042A000000133003005600000001000011160A2B43\
  68. 036F1D00000A027B04000004066F1E00000A6F1D00000A321603027B04000004066F1E00000A196F1F00000A162F10027B04000004066F2000000A0617590A061758\
  69. 0A06027B040000046F1C00000A32AF2A000013300300A100000002000011160B036F1D00000A046F1D00000A2F08040A031002061001046F1D00000A0D2B7A161304\
  70. 2B65041104096F2100000A0A0306196F1F00000A16324A072D02090B160C1613052B20027B0400000411056F1E00000A06196F2200000A2C04170C2B151105175813\
  71. 051105027B040000046F1C00000A32D1082D0C027B04000004066F2300000A11041758130411040958046F1D00000A318F0917590D09163082072A00000013300200\
  72. 7000000003000011160A732400000A0B160C2B54027B04000004086F1E00000A6F1D00000A063121076F2500000A07086F2600000A027B04000004086F1E00000A6F\
  73. 1D00000A0A2B1B027B04000004086F1E00000A6F1D00000A06330707086F2600000A0817580C08027B040000046F1C00000A329E072A13300300BF01000004000011\
  74. 02177D05000004027B010000042D090F017B010000042C1E02177D01000004027B040000046F1B00000A027E1200000A7D020000042A027B0300000439C000000002\
  75. 7B020000047E1200000A281A00000A2C2C0F017B030000042C0E020F017B020000047D020000042A02167D03000004020F017B040000047D040000042A0F017B0300\
  76. 00042C360F017B020000047E1200000A282700000A2C65020F017B02000004282800000A027C06000004282900000A282A00000A28020000062A027B020000040A02\
  77. 7E1200000A7D0200000402167D03000004020F017B040000047D040000040206282800000A027C06000004282900000A282A00000A28020000062A0F017B03000004\
  78. 2C390F017B020000047E1200000A282700000A399E000000020F017B02000004282800000A027C06000004282900000A282A00000A28020000062A160C2B55160B16\
  79. 0D2B29027B04000004086F1E00000A0F017B04000004096F1E00000A19282B00000A2C04170B2B130917580D090F017B040000046F1C00000A32C8072D10027B0400\
  80. 0004086F2000000A0817590C0817580C08027B040000046F1C00000A329D027B040000046F1C00000A2D0702177D010000042A00133004004501000005000011027B\
  81. 010000042C2A7201000070027C05000004282C00000A7221000070282D00000A732E00000A282F00000A733000000A2A027B010000042D0E027B030000042C062831\
  82. 00000A2A0228050000060A733200000A0B0772370000706F3300000A0C087243000070027C05000004282C00000A6F3400000A027C06000004282900000A2D42160D\
  83. 2B330772510000706F3300000A13041104027B0400000406096F3500000A6F1E00000A6F3600000A0811046F3700000A260917580D09066F3800000A32C42B611613\
  84. 052B4D0772510000706F3300000A13061106027B0400000411056F1E00000A6F3600000A1106725B0000700611056F3900000A13071207282C00000A6F3400000A08\
  85. 11066F3700000A261105175813051105027B040000046F1C00000A32A407086F3700000A26076F3A00000A732E00000A282F00000A733000000A2A00000013300200\
  86. 790000000600001102036F3B00000A731600000A7D0600000402036F3B00000A7D0500000402036F3B00000A7D01000004027B010000042D4702036F3C00000A7D02\
  87. 00000402036F3B00000A7D03000004036F3D00000A0A02731300000A7D04000004160B2B15027B04000004036F3C00000A6F2300000A0717580B070632E72A000000\
  88. 13300300830000000100001103027C06000004282900000A6F3E00000A03027B050000046F3E00000A03027B010000046F3E00000A027B010000042D5103027B0200\
  89. 00046F3F00000A03027B030000046F3E00000A03027B040000046F1C00000A6F4000000A160A2B1603027B04000004066F1E00000A6F3F00000A0617580A06027B04\
  90. 0000046F1C00000A32DC2A0042534A4201000100000000000C00000076322E302E35303732370000000005006C00000060040000237E0000CC040000300600002353\
  91. 7472696E677300000000FC0A000070000000235553006C0B00001000000023475549440000007C0B0000A802000023426C6F62000000000000000200000157170208\
  92. 0900000000FA25330016000001000000260000000200000006000000090000000800000001000000400000000D000000060000000300000001000000030000000000\
  93. 0A0001000000000006005B0054000A008C0071000600E400C9000600FF0054000A00390124010A00430124010A00980124010600B301A9010600C501A9010A00E301\
  94. 710006004B02390206006202390206007F02390206009E0239020600B70239020600D00239020600EB02390206000603390206001F03390206003803390206006803\
  95. 550357007C0300000600AB038B030600CB038B0306000F0454000A00250471000A004604710006006C044D04060082044D0406008D0454000600FC04540006004D05\
  96. 540006006505A9010E007D05720506008705A9010E00A20572050E00AE0572050E00D405720500000000010000000000010001000921100034000000050001000100\
  97. 01009D000A000100A6000D000100B6000A000100EB0010000100F4000A0001000A01170050200000000086001F011E0001008C200000000086004E01220001008C21\
  98. 00000000810059012A000300F0210000000081006E012F000400A0220000000081008001350006001C2300000000860092013D000600E8240000000086009F014300\
  99. 07003C2600000000E601C00148000700C42600000000E601D2014E00080000000100D80100000200FB01000001000F02000001000F02000002001802000001002202\
  100. 000001002B02000001003202020009005100F5011E005900F5012A006100F5012A006900F5012A007100F5012A007900F5012A008100F5012A008900F5012A009100\
  101. F5012A009900F5012A00A100F5012A00A900F5016700B900F5016D00C100F5011E00C900F5011E00D100F5017200E100F501EB00F10094040D000C00F5011E001400\
  102. 9A04FD003100A704FD001400F50101012900B204FD002900BD040701F100C7040701F100CC040B010C00D8041E000C00DE041101F100E80411010C00F3041501F100\
  103. 0D051B010C0015056D00F1001E052601F10028052C010C002F0501011C00F5011E001C00D8041E001C002F050101F10033050B01290041054C011400BD0452013100\
  104. 41055701F10028055D01010155050701F1005E056C010901F5012A001101920573013900F5017C013900990583012101F5011E002101B90588012901C7058F011C00\
  105. F30415013101DC052A003101EA0595011C00DE0411011C00F6059E013101FF05070141000C06FD004100180607014100230611014900D201C0014900D2012A004900\
  106. D2016D0024000B0054002E00330012022E001300C5012E001B00F6012E002300F6012E002B00FC012E005B0069022E00730086022E004300F6012E003B002A022E00\
  107. 630074022E006B007D024300830078002201330142016501A401BB01F100F7003C01048000000100000003000000000000000000E903000002000000000000000000\
  108. 000001004B0000000000020000000000000000000000010065000000000002000000000000000000000001007205000000000000003C4D6F64756C653E0053716C51\
  109. 75616E74756D4C6561702E4C6F6E67657374436F6D6D6F6E537562737472696E672E646C6C004C6F6E67657374436F6D6D6F6E537562737472696E67006D73636F72\
  110. 6C69620053797374656D0056616C7565547970650053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E536572766572004942696E6172\
  111. 7953657269616C697A65005F4973456D707479005F54656D70466972737456616C7565005F49734669727374436F6D70617269736F6E0053797374656D2E436F6C6C\
  112. 656374696F6E732E47656E65726963004C6973746031005F4D617463686573005F4861734D6572676564004E756C6C61626C656031005F52657475726E416C6C5375\
  113. 62737472696E677300496E69740053797374656D2E446174612E53716C54797065730053716C537472696E670053716C426F6F6C65616E00416363756D756C617465\
  114. 0052656D6F76654D697373696E674D6174636865730045787472616374537562737472696E6773004765744D61784C656E6774684974656D73004D65726765005371\
  115. 6C586D6C005465726D696E6174650053797374656D2E494F0042696E61727952656164657200526561640042696E61727957726974657200577269746500536F6D65\
  116. 537472696E670053716C4661636574417474726962757465002E63746F720052657475726E416C6C537562737472696E677300536561726368496E00536561726368\
  117. 466F7200496E636F6D696E6700526561646572005772697465720053797374656D2E5265666C656374696F6E00417373656D626C795469746C654174747269627574\
  118. 6500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650041737365\
  119. 6D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F70797269676874417474\
  120. 72696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C\
  121. 7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F737469637300\
  122. 44656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C65725365727669636573\
  123. 00436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C\
  124. 5175616E74756D4C6561702E4C6F6E67657374436F6D6D6F6E537562737472696E670053657269616C697A61626C654174747269627574650053716C557365724465\
  125. 66696E656441676772656761746541747472696275746500466F726D61740053797374656D2E52756E74696D652E496E7465726F7053657276696365730053747275\
  126. 63744C61796F7574417474726962757465004C61796F75744B696E6400537472696E6700456D707479006765745F48617356616C7565006765745F49735472756500\
  127. 6765745F49734E756C6C006765745F56616C7565005472696D006F705F457175616C69747900436C656172006765745F436F756E74006765745F4C656E6774680067\
  128. 65745F4974656D00537472696E67436F6D70617269736F6E00496E6465784F660052656D6F7665417400537562737472696E6700457175616C7300416464006F705F\
  129. 496E657175616C697479006F705F496D706C6963697400426F6F6C65616E00546F537472696E6700436F6E63617400537472696E675265616465720053797374656D\
  130. 2E586D6C00586D6C526561646572005465787452656164657200437265617465006765745F4E756C6C00586D6C446F63756D656E7400586D6C456C656D656E740043\
  131. 7265617465456C656D656E740053657441747472696275746500586D6C4E6F6465007365745F496E6E65725465787400417070656E644368696C6400436F6E746169\
  132. 6E73006765745F4F75746572586D6C0052656164426F6F6C65616E0052656164537472696E670052656164496E74333200000000001F3C004900740065006D007300\
  133. 20004D00650072006700650064003D002200001522003E003C002F004900740065006D0073003E00000B4900740065006D007300000D4D0065007200670065006400\
  134. 00094900740065006D000013490073004C006F006E00670065007300740000006FF0AE25CB40BE4A8B4B7FA071797C920008B77A5C561934E08902060202060E0606\
  135. 15120D010E06061511110102032000010720020111151119042001010E052002080E0E07200015120D0108052001011108042000121D052001011221052001011225\
  136. 12010001005408074D617853697A65A00F0000052001011159042001010805200101116D7201000200000005005402174973496E76617269616E74546F4475706C69\
  137. 6361746573015402124973496E76617269616E74546F4E756C6C73015402124973496E76617269616E74546F4F726465720154020D49734E756C6C4966456D707479\
  138. 0054080B4D61784279746553697A65FFFFFFFF0520010111750515120D010E051511110102032000020520010113000320000E050002020E0E032000080520011300\
  139. 08062002080E117D030701080520020E0808062002020E117D0807060E08020808080515120D01080907030815120D01080805000111150E04200013000500011119\
  140. 02070003020E0E117D0607040E0208080600030E0E0E0E08000112808912808D06200101128089040000121D0620011280950E052002010E0E082001128099128099\
  141. 05200102130016070815120D0108128091128095081280950812809502040702080804200101023001002B4C6F6E6765737420436F6D6D6F6E20537562737472696E\
  142. 67204167677265676174652046756E6374696F6E00000501000000001501001053716C205175616E74756D204C656170000017010012537461697277617920746F20\
  143. 53514C434C5200003E010039436F707972696768742028632920323031362053716C205175616E74756D204C6561702E20416C6C2072696768747320726573657276\
  144. 65642E00000A010005312E302E3300000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100\
  145. 0000000000006604EA5600000000020000001C0100009435000094170000525344531BAAEB2CE127CD4DB57697A0E30FF77809000000633A5C50686F656E69785072\
  146. 6F6A6563745C41727469636C65735C5374616972776179546F53514C434C525C4C6576656C2D313220557365722D446566696E656420416767726567617465735C43\
  147. 6F64655C5374616972776179546F53514C434C522D31325F5544415C5374616972776179546F53514C434C522D31325F5544415C6F626A5C52656C656173655C5371\
  148. 6C5175616E74756D4C6561702E4C6F6E67657374436F6D6D6F6E537562737472696E672E706462000000000000000000000000000000000000000000000000000000\
  149. 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000D83600000000000000000000EE360000\
  150. 002000000000000000000000000000000000000000000000E03600000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020\
  151. 001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  152. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  153. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  154. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  155. 000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000\
  156. 040400000000000000000000040434000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000\
  157. 030000000100000003003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00\
  158. 000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00464030000010053007400720069006E006700460069006C006500\
  159. 49006E0066006F00000040030000010030003000300030003000340062003000000044001100010043006F006D00700061006E0079004E0061006D00650000000000\
  160. 530071006C0020005100750061006E00740075006D0020004C006500610070000000000080002C000100460069006C00650044006500730063007200690070007400\
  161. 69006F006E00000000004C006F006E006700650073007400200043006F006D006D006F006E00200053007500620073007400720069006E0067002000410067006700\
  162. 7200650067006100740065002000460075006E006300740069006F006E0000002C0006000100460069006C006500560065007200730069006F006E00000000003100\
  163. 2E0030002E003300000074002A00010049006E007400650072006E0061006C004E0061006D0065000000530071006C005100750061006E00740075006D004C006500\
  164. 610070002E004C006F006E00670065007300740043006F006D006D006F006E0053007500620073007400720069006E0067002E0064006C006C00000098003A000100\
  165. 4C006500670061006C0043006F007000790072006900670068007400000043006F007000790072006900670068007400200028006300290020003200300031003600\
  166. 2000530071006C0020005100750061006E00740075006D0020004C006500610070002E00200041006C006C0020007200690067006800740073002000720065007300\
  167. 650072007600650064002E0000007C002A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C00510075006100\
  168. 6E00740075006D004C006500610070002E004C006F006E00670065007300740043006F006D006D006F006E0053007500620073007400720069006E0067002E006400\
  169. 6C006C000000480013000100500072006F0064007500630074004E0061006D0065000000000053007400610069007200770061007900200074006F00200053005100\
  170. 4C0043004C00520000000000300006000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0033000000380008000100\
  171. 41007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0033002E003000000000000000000000000000000000000000\
  172. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  173. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  174. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  175. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  176. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  177. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  178. 0000000000000000003000000C0000000037000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  179. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  180. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  181. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  182. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  183. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  184. 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000\
  185. 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  186.     WITH PERMISSION_SET = SAFE;
  187.  
  188. GO
  189. PRINT N'Creating [dbo].[LongestCommonSubstring] Aggregate Function...';
  190.  
  191. GO
  192. CREATE AGGREGATE [dbo].[LongestCommonSubstring](@SomeString NVARCHAR (4000), @ReturnAllSubstrings BIT)
  193.     RETURNS XML
  194.     EXTERNAL NAME [SqlQuantumLeap.LongestCommonSubstring].[LongestCommonSubstring];
  195. GO
  196.  
  197. -------------------------------------------------------------------------------------
RAW Paste Data