/SqlServerRegexFunctions

Regular expressions as user-defined functions in SQL Server

Primary LanguageC#

Regular Expressions (Regex) Functions for SQL Server

An implementation of .NET Regular Expressions for use in SQL Server. All C# code is based on code from the Just geeks blog.

The SQL script creates T-SQL wrapper functions that call the functions in the CLR assembly.

Available functions

  • RegexMatch - returns 1 if pattern can be found in input, else 0
  • RegexReplace - replaces all matches in input with a specified string
  • RegexSelectOne - returns the first, second, third, etc match that can be found in the input
  • RegexSelectAll - returns all matches delimited by separator that can be found in the input

Usage examples

-- Returns 1 in this case since the phone number pattern is matched
select dbo.RegexMatch( N'123-45-6749', N'^\d{3}-\d{2}-\d{4}') 

-- Returns 137 since all alpha characters where replaced with no characters
select dbo.RegExReplace('Remove1All3Letters7','[a-zA-Z]','') 

-- Returns 123-45-6789 since first match was specifed. If last parameter was 1 then the second match (222-33-4444) would be returned.
select dbo.RegexSelectOne('123-45-6749xxx222-33-4444', '\d{3}-\d{2}-\d{4}', 0) 

-- Returns 123-45-6749|222-33-4444 
select dbo.RegexSelectAll('123-45-6749xxx222-33-4444', '\d{3}-\d{2}-\d{4}', '|')

Installation

  1. Build the RegexFunctions project in Visual Studio.
  2. In the build output folder, run the "generate-assembly-script.ps1" script which generates a "CreateAssembly.sql" file.
  3. In SQL Server, run the "CreateAssembly.sql" file.
  4. Run the "SQL Server Objects\Create UserDefinedFunctions.sql" script to add the SQL Server wrapper functions.