吾爱破解 - 52pojie.cn

 找回密码
 注册[Register]

QQ登录

只需一步,快速开始

查看: 1527|回复: 20
收起左侧

[原创工具] SQLSERVER自定义正则匹配函数

  [复制链接]
candyl6 发表于 2024-7-22 23:04
本帖最后由 candyl6 于 2024-7-22 23:08 编辑

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

PixPin_2024-07-22_23-02-10.png
dll文件内容如下:
PixPin_2024-07-22_22-59-08.png
RegexAssembly.zip (1.53 KB, 下载次数: 13)

免费评分

参与人数 1吾爱币 +1 收起 理由
wgcg513 + 1 我很赞同!

查看全部评分

发帖前要善用论坛搜索功能,那里可能会有你要找的答案或者已经有人发布过相同内容了,请勿重复发帖。

maike233 发表于 2024-7-22 23:42
感谢楼主分享
keepxcw 发表于 2024-7-23 00:23
t77m5313 发表于 2024-7-23 00:29
头像被屏蔽
ootd123 发表于 2024-7-23 00:29
提示: 作者被禁止或删除 内容自动屏蔽
Carinx 发表于 2024-7-23 08:26
我没记错的话,sqlserver的like和patindex也可以进行简单的正则匹配
louther 发表于 2024-7-23 08:57
学习一下SQL
ilpj 发表于 2024-7-23 09:00
学习了,感谢楼主分享
binger04 发表于 2024-7-23 09:07
感谢楼主分享
fxfxs 发表于 2024-7-23 09:23
谢谢分享,SQL学习中
您需要登录后才可以回帖 登录 | 注册[Register]

本版积分规则

返回列表

RSS订阅|小黑屋|处罚记录|联系我们|吾爱破解 - LCG - LSG ( 京ICP备16042023号 | 京公网安备 11010502030087号 )

GMT+8, 2024-12-14 13:26

Powered by Discuz!

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表