SQLSERVER自定义正则匹配函数

candyl6

sqlserver数据库没有自带正则匹配函数，可以创建CLR程序集来实现，这里使用的是C#

先上创建方法

1. 启用数据库的 CLR 集成，默认是关闭的

   sp_configure 'clr enabled', 1;
   RECONFIGURE;

   2.创建程序集，这里FROM后面的内容其实就是将dll文件转换后的十六进制

   CREATE ASSEMBLY RegexAssembly
   FROM 0x4d5a90000300000004000000ffff0000b800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000e1fba0e00b409cd21b8014ccd21546869732070726f6772616d2063616e6e6f742062652072756e20696e20444f53206d6f64652e0d0d0a2400000000000000504500004c0103007ad69d660000000000000000e00022200b0130000008000000060000000000006e2600000020000000400000000000100020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001c2600004f00000000400000b802000000000000000000000000000000000000006000000c000000e42400001c0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002e7465787400000074060000002000000008000000020000000000000000000000000000200000602e72737263000000b80200000040000000040000000a0000000000000000000000000000400000402e72656c6f6300000c0000000060000000020000000e0000000000000000000000000000400000420000000000000000000000000000000050260000000000004800000002000500a4200000400400000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002003d00000001000011000f00280500000a2d090f01280500000a2b01170a062c087e0600000a0b2b1b0f00280700000a0f01280700000a280800000a280900000a0b2b00072a2202280a00000a002a000042534a4201000100000000000c00000076322e302e35303732370000000005006c00000070010000237e0000dc010000b801000023537472696e67730000000094030000040000002355530098030000100000002347554944000000a80300009800000023426c6f620000000000000002000001471502000900000000fa01330016000001000000090000000200000002000000020000000a000000040000000100000001000000030000000000ad0001000000000006004800180106006800180106001f0005010f003801000006008a01ca000a003300e4000a00d10047010a00900047010e00a3015c010000000001000000000001000100010010007b017b011500010001005020000000009600a200370001009920000000008618ff0006000300000001009d0100000200dc000900ff0001001100ff0006001900ff000a003100ff0006004100bf0016003900c5001a00410086001e0049009a0022003900910128002900ff0006002000230071002e000b0040002e00130049002e001b0068001000048000000000000000000000000000000000a90100000200000000000000000000002e001600000000000200000000000000000000002e000a00000000000200000000000000000000002e00ca000000000000000000003c4d6f64756c653e0053797374656d2e44617461006d73636f726c69620044656275676761626c654174747269627574650053716c46756e6374696f6e41747472696275746500436f6d70696c6174696f6e52656c61786174696f6e734174747269627574650052756e74696d65436f6d7061746962696c697479417474726962757465006765745f56616c75650053716c537472696e670049734d617463680052656765784d61746368005265676578417373656d626c792e646c6c006765745f49734e756c6c0053797374656d0053716c426f6f6c65616e007061747465726e004d6963726f736f66742e53716c5365727665722e536572766572002e63746f720053797374656d2e446961676e6f73746963730053797374656d2e52756e74696d652e436f6d70696c6572536572766963657300446562756767696e674d6f6465730053797374656d2e446174612e53716c54797065730053797374656d2e546578742e526567756c617245787072657373696f6e7300526567657846756e6374696f6e73004f626a656374006f705f496d706c6963697400696e707574005265676578005265676578417373656d626c79000000000000906319ed7a71cc4cb6b8b69a58240fd40004200101080320000105200101111105070202111d032000020306111d0320000e050002020e0e050001111d0208b77a5c561934e089080002111d112111210801000800000000001e01000100540216577261704e6f6e457863657074696f6e5468726f777301080100070100000000240100020054020f497344657465726d696e697374696301540209497350726563697365010000000000007ad69d6600000000020000001c010000002500000007000052534453158cfb39f1e348479e2afe7e0b935d4c01000000453a5c4a6f686e5c446f63756d656e74735c56697375616c2053747564696f20323032325c50726f6a656374735c526567657846756e6374696f6e735c6f626a5c44656275675c5265676578417373656d626c792e706462000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000004426000000000000000000005e26000000200000000000000000000000000000000000000000000050260000000000000000000000005f436f72446c6c4d61696e006d73636f7265652e646c6c0000000000ff2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000005c02000000000000000000005c0234000000560053005f00560045005200530049004f004e005f0049004e0046004f0000000000bd04effe00000100000000000000000000000000000000003f000000000000000400000002000000000000000000000000000000440000000100560061007200460069006c00650049006e0066006f00000000002400040000005400720061006e0073006c006100740069006f006e00000000000000b004bc010000010053007400720069006e006700460069006c00650049006e0066006f0000009801000001003000300030003000300034006200300000002c0002000100460069006c0065004400650073006300720069007000740069006f006e000000000020000000300008000100460069006c006500560065007200730069006f006e000000000030002e0030002e0030002e003000000044001200010049006e007400650072006e0061006c004e0061006d00650000005200650067006500780041007300730065006d0062006c0079002e0064006c006c0000002800020001004c006500670061006c0043006f0070007900720069006700680074000000200000004c00120001004f0072006900670069006e0061006c00460069006c0065006e0061006d00650000005200650067006500780041007300730065006d0062006c0079002e0064006c006c000000340008000100500072006f006400750063007400560065007200730069006f006e00000030002e0030002e0030002e003000000038000800010041007300730065006d0062006c0079002000560065007200730069006f006e00000030002e0030002e0030002e00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000c000000703600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
   WITH PERMISSION_SET = SAFE;

   3.创建函数，这里RegexAssembly.[RegexFunctions.RegexFunctions].RegexMatch要按照程序集名.[namespace.类名].方法名方式补充完整

   CREATE FUNCTION dbo.RegexMatch( @input NVARCHAR(MAX), @pattern NVARCHAR(MAX))
   RETURNS BIT
   AS EXTERNAL NAME RegexAssembly.[RegexFunctions.RegexFunctions].RegexMatch

   4.测试

   SELECT dbo.RegexMatch('hello word','hello');--匹配成功返回1

dll文件内容如下:

RegexAssembly.zip (1.53 KB, 下载次数: 13)

2024-7-22 23:06 上传

点击文件名下载附件

maike233

感谢楼主分享

keepxcw

非常感谢楼主分享

t77m5313

我去试试，感谢楼主分享

ootd123

感谢楼主分享

likunjava

感谢楼主分享

a5668685

感谢楼主分享~

Carinx

我没记错的话，sqlserver的like和patindex也可以进行简单的正则匹配

louther

学习一下SQL

ilpj

学习了，感谢楼主分享