|
This example covers creating a generic user defined function to allow the use of regular expressions in SQL Server for pattern matching. This can then replace the pattern matching in tsql. This function can be used as the basis for custom check constraints on table columns as well.
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
public partial class RegexFunctions
{
[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static bool RegExMatch(string pattern, string matchString)
{
Regex r1 = new Regex(pattern.TrimEnd(null));
return r1.Match(matchString.TrimEnd(null)).Success;
}
}
|
To use the above function you will need to have an existing Regular Expressions pattern to test with. You can use this to validate that an IP Address is valid with the following regex pattern:
\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b
DECLARE @pattern varchar(100)
SET @pattern = '\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b'
SELECT dbo.RegExMatch(@pattern, '10.254.100.12')
SELECT dbo.RegExMatch(@pattern, '999.999.999.999') |
|