README.md

AzureSQLMI-CLR-Sample

Create and run custom regex CLR function in Azure SQL MI

Convert DLL from binary to HEX

HexSqlCLR.exe c:\file-path-to.dll

Create a SqlClrRegEx function in Azure SQL MI

I suggest downloading and building your own binary in visual studio, but you could also sample code below

ALTER DATABASE SQLTestDB SET TRUSTWORTHY ON;

CREATE ASSEMBLY [SqlClrRegEx]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300DD4CD65D0000000000000000E00022200B013000000C00000006000000000000AA2A0000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000582A00004F000000004000008803000000000000000000000000000000000000006000000C000000202900001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000B00A000000200000000C000000020000000000000000000000000000200000602E72737263000000880300000040000000040000000E0000000000000000000000000000400000402E72656C6F6300000C00000000600000000200000012000000000000000000000000000040000042000000000000000000000000000000008C2A0000000000004800000002000500202100000008000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330020019000000010000110003731000000A0A06026F1100000A6F1200000A0B2B00072A0000001330030013000000020000110002031E281300000A6F1200000A0A2B00062A001330020019000000030000110003731000000A0A06026F1100000A6F1400000A0B2B00072A0000001330030013000000040000110002031E281300000A6F1400000A0A2B00062A00133003000E0000000400001100020304281500000A0A2B00062A0000133004000F00000004000011000203041E281600000A0A2B00062A2202281700000A002A42534A4201000100000000000C00000076342E302E33303331390000000005006C00000000030000237E00006C0300000C03000023537472696E6773000000007806000004000000235553007C0600001000000023475549440000008C0600007401000023426C6F620000000000000002000001471502000900000000FA013300160000010000001600000002000000070000000E00000017000000140000000400000001000000030000000000020201000000000006002D018C0206009A018C0206004C005A020F00AC0200000600740019020600100119020600DC00190206008101190206004D01190206006601190206008B001902060060006D0206003E006D020600BF0019020600A600C8010600F30212020A00FB0039020E000603BB020E00FC01BB020E003302BB020E00DA02BB020E003600BB02000000000100000000000100010001001000000300004100010001005020000000009600FC015E0001007820000000009600F4015E0003009820000000009600EA0164000500C020000000009600E20164000700E020000000009600270045000900FC200000000096001F0045000C001721000000008618540206000F00000001002F00000002002B02000001002F00000002002B02000001002F00000002002B02000001002F00000002002B02000001002F00000002002B0200000300C201000001002F00000002002B0200000300C201090054020100110054020600190054020A002900540210003100540210003900540210004100540210004900540210005100540210005900540210006100540215006900540210007100540210007900540210008900540206009100540210009100FC012000A100E70226009100FC012E00B100B8013D00910027004500910027004C0081005402060020007B004F012E000B006A002E00130073002E001B0092002E0023009B002E002B00AC002E003300AC002E003B00AC002E0043009B002E004B00B2002E005300AC002E005B00AC002E006300CA002E006B00F4002E007300010140007B004F0160007B004F0180007B004F01A0007B004F01C0007B004F011A002A0037004100048000000100000000000000000000000000FA020000040000000000000000000000550016000000000004000000000000000000000055000A0000000000040000000000000000000000550012020000000000000000003C4D6F64756C653E0053797374656D2E44617461006D73636F726C696200436F6D70696C65645265706C61636500736F75726365004361707475726500477569644174747269627574650044656275676761626C6541747472696275746500436F6D56697369626C6541747472696275746500417373656D626C795469746C6541747472696275746500417373656D626C7954726164656D61726B417474726962757465005461726765744672616D65776F726B41747472696275746500417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E4174747269627574650053716C46756E6374696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E7341747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C79436F6D70616E794174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650076616C75650053797374656D2E52756E74696D652E56657273696F6E696E6700436F6D70696C6564537562737472696E6700436F6D70696C65644D617463680053716C436C7252656745782E646C6C0053797374656D0053797374656D2E5265666C656374696F6E007061747465726E0047726F7570004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E496E7465726F7053657276696365730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E546578742E526567756C617245787072657373696F6E730052656765784F7074696F6E73006765745F53756363657373004F626A6563740053716C436C7252656745780052656765780000000000C5ADAA3DF784504CB8F28FE96B9A612600042001010803200001052001011111042001010E0420010102050702124902052001124D0E0320000203070102080003124D0E0E115505070212490E0320000E0307010E0600030E0E0E0E0800040E0E0E0E115508B77A5C561934E089050002020E0E0500020E0E0E0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000001001000B53716C436C725265674578000005010000000017010012436F7079726967687420C2A920203230313700002901002432363565306263332D616435662D343466332D626231372D63363166373762393834376600000C010007312E302E302E3000004D01001C2E4E45544672616D65776F726B2C56657273696F6E3D76342E362E310100540E144672616D65776F726B446973706C61794E616D65142E4E4554204672616D65776F726B20342E362E31240100020054020F497344657465726D696E6973746963015402094973507265636973650100000000DD4CD65D00000000020000001C0100003C2900003C0B0000525344532B41BCABAD83D5439919A975C23BDA5F01000000583A5C706F635C666C6578695C73716C2D7365727665722D73616D706C65735C73616D706C65735C66656174757265735C73716C2D636C725C52656745785C6F626A5C44656275675C53716C436C7252656745782E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000802A000000000000000000009A2A00000020000000000000000000000000000000000000000000008C2A0000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500200010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001001000000018000080000000000000000000000000000001000100000030000080000000000000000000000000000001000000000048000000584000002C03000000000000000000002C0334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B0048C020000010053007400720069006E006700460069006C00650049006E0066006F0000006802000001003000300030003000300034006200300000001A000100010043006F006D006D0065006E007400730000000000000022000100010043006F006D00700061006E0079004E0061006D006500000000000000000040000C000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0043006C007200520065006700450078000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000040001000010049006E007400650072006E0061006C004E0061006D0065000000530071006C0043006C007200520065006700450078002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100370000002A00010001004C006500670061006C00540072006100640065006D00610072006B00730000000000000000004800100001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0043006C007200520065006700450078002E0064006C006C00000038000C000100500072006F0064007500630074004E0061006D00650000000000530071006C0043006C007200520065006700450078000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E003000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000AC3A00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
GO

CREATE SCHEMA REGEX;
GO

--Create the functions
CREATE FUNCTION REGEX.MATCH (@src NVARCHAR(MAX), @regex NVARCHAR(4000))
RETURNS BIT
AS EXTERNAL NAME SqlClrRegEx.RegEx.CompiledMatch
GO
CREATE FUNCTION REGEX.SUBSTRING (@src NVARCHAR(MAX), @regex NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS EXTERNAL NAME SqlClrRegEx.RegEx.CompiledSubstring
GO
CREATE FUNCTION REGEX.REPLACE (@src NVARCHAR(MAX), @regex NVARCHAR(MAX), @value NVARCHAR(4000))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME SqlClrRegEx.RegEx.CompiledReplace
GO

Test regex function

IF( REGEX.MATCH('tst123test', '[0-9]+') = 1 )
	SELECT REGEX.SUBSTRING('tst123test', '[0-9]+'), REGEX.REPLACE('tst123test', '[0-9]+', 'XXX')