Network Programming in .NET

You can do very basic string matching using the LIKE operator in SQL server, but you quickly hit the limits of what you can easily do using the simple operations supported. .NET offers full Regex (Regular expression) support, and you can use this within SQL server by creating a UDF CLR (User defined function).

TL;DR; here is the Github repo: https://github.com/infiniteloopltd/SQLServerRegex

Which defines this one function;

using System.Data.SqlTypes;
using RX = System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Regex(string input, string regex)
{
var match = RX.Regex.Match(input, regex).Groups[1].Value;
return new SqlString (match);
}
}

Which you can install into your SQL server database as follows;

EXEC sp_changedbowner ‘sa’
ALTER DATABASE [<your database here>] SET trustworthy ON

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO

sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO

CREATE ASSEMBLY [DbRegex] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C01030079D4F85D0000000000000000E00022200B0130000008000000060000000000003A260000002000000040000000000010002000000002000004000000000000000600000000000000008000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000E82500004F00000000400000A002000000000000000000000000000000000000006000000C000000B02400001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000040060000002000000008000000020000000000000000000000000000200000602E72737263000000A00200000040000000040000000A0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000000E000000000000000000000000000040000042000000000000000000000000000000001C2600000000000048000000020005008C200000240400000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133002002400000001000011000203280500000A6F0600000A176F0700000A6F0800000A0A06730900000A0B2B00072A2202280A00000A002A00000042534A4201000100000000000C00000076342E302E33303331390000000005006C00000080010000237E0000EC010000AC01000023537472696E6773000000009803000004000000235553009C030000100000002347554944000000AC0300007800000023426C6F620000000000000002000001471502000900000000FA013300160000010000000C0000000200000002000000020000000A000000040000000100000001000000030000000000A800010000000000060050000E01060070000E0106002700FB000F002E01000006009101BD000A003B00DA000A0098003D010E00A00152010E00A20052010E00C40052010E00D40052010E001F005201000000000100000000000100010001001000710100001500010001005020000000009600A0013A0001008020000000008618F5000600030000000100980100000200A6010900F50001001100F50006001900F5000A003100F50006004100A2001600490086011D005100B400220061008E0028003900F5002C002900F50006002000230072002E000B0041002E0013004A002E001B00690010000480000000000000000000000000000000009E010000040000000000000000000000310016000000000004000000000000000000000031000A00000000000400000000000000000000003100BD00000000000000003C4D6F64756C653E0053797374656D2E44617461006D73636F726C696200436170747572650044656275676761626C654174747269627574650053716C46756E6374696F6E41747472696275746500436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465006765745F56616C75650053716C537472696E67004D6174636800446252656765782E646C6C006765745F4974656D0053797374656D0047726F7570436F6C6C656374696F6E0047726F7570004D6963726F736F66742E53716C5365727665722E536572766572002E63746F720053797374656D2E446961676E6F73746963730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300446562756767696E674D6F6465730053797374656D2E446174612E53716C54797065730053797374656D2E546578742E526567756C617245787072657373696F6E730055736572446566696E656446756E6374696F6E73006765745F47726F757073004F626A65637400696E707574004462526567657800726567657800000000000F4DE26091FA75478EA1FCAB7323A50F000420010108032000010520010111110507020E111D06000212250E0E0420001229052001122D080320000E042001010E08B77A5C561934E089060002111D0E0E0801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F7773010801000701000000000401000000000000000079D4F85D00000000020000001C010000CC240000CC06000052534453DAF00DA62F5BD1478B583CA3D74FE38B01000000433A5C55736572735C46696163685C736F757263655C7265706F735C446252656765785C446252656765785C6F626A5C44656275675C446252656765782E7064620000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001026000000000000000000002A2600000020000000000000000000000000000000000000000000001C260000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000440200000000000000000000440234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004A4010000010053007400720069006E006700460069006C00650049006E0066006F0000008001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000038000C00010049006E007400650072006E0061006C004E0061006D006500000044006200520065006700650078002E0064006C006C0000002800020001004C006500670061006C0043006F00700079007200690067006800740000002000000040000C0001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000044006200520065006700650078002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C0000003C3600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE

CREATE FUNCTION [dbo].[regex] (@text [nvarchar](MAX), @regex [nvarchar](MAX))
RETURNS [nvarchar](MAX)
AS EXTERNAL NAME [DbRegex].[UserDefinedFunctions].[Regex]

Once that’s done, then you can run SQL commands like;

select dbo.regex(‘1234abc123′,’\d+([a-z]+)\d+’)