Ascentis.RegExSQL 1.1.1.9

mssql-regex

This package allows to use CLR RegEx class within MSSQL.

The package caches compiled Regex objects based on regex pattern and options parameter when specific version of the APIs is used. Regex objects are taken from the cache, used and returned back to the cache. By default they have a TTL of 5 minutes. There's an initial cost upon executing a new regex as the regex expression is compiled into intermediate language opcodes and cached for reuse.

The functions return either a scalar string value or they return a table of strings containing each match.

There's a cleanup timer that runs every 10 seconds and it will cleanup all regex stacks not used in the last 5 minutes.

The library provides a set of functions to inspect the status of the cache and the number of executions performed since the library was loaded. Methods to reset the stats are provided.

For general reference on regular expression language as supported by Microsoft see: https://docs.microsoft.com/en-us/dotnet/standard/base-types/regular-expression-language-quick-reference

For reference on Regex class see: https://docs.microsoft.com/en-us/dotnet/api/system.text.regularexpressions.regex?view=netframework-4.6.1

Nuget repo with packaged up ready made .sql scripts: https://www.nuget.org/packages/Ascentis.RegExSQL/

Functions exposed:

Matching functions returning a scalar value:

CREATE FUNCTION RegExIsMatch(
    @input nvarchar(max), 
    @pattern nvarchar(max)) RETURNS bit
CREATE FUNCTION RegExIsMatchWithOptions(
    @input nvarchar(max), 
    @pattern nvarchar(max), 
    @options int) RETURNS bit
CREATE FUNCTION RegExMatch(
    @input nvarchar(max), 
    @pattern nvarchar(max)) RETURNS nvarchar(max)
CREATE FUNCTION RegExMatchWithOptions(
    @input nvarchar(max), 
    @pattern nvarchar(max), 
    @options int) RETURNS nvarchar(max)
CREATE FUNCTION RegExMatchIndexed(
    @input nvarchar(max), 
    @pattern nvarchar(max), 
    @index int) RETURNS nvarchar(max)
CREATE FUNCTION RegExMatchIndexedWithOptions(
    @input nvarchar(max), 
    @pattern nvarchar(max), 
    @index int, 
    @options int) RETURNS nvarchar(max)
CREATE FUNCTION RegExMatchGroup(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @group int) RETURNS nvarchar(max)
CREATE FUNCTION RegExMatchGroupWithOptions(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @group int,
    @options int) RETURNS nvarchar(max)
CREATE FUNCTION RegExMatchGroupIndexed(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @group int,
    @index int) RETURNS nvarchar(max)
CREATE FUNCTION RegExMatchGroupIndexedWithOptions(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @group int,
    @index int,
    @options int) RETURNS nvarchar(max)

Replacement functions

CREATE FUNCTION RegExReplace(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @replacement nvarchar(max)) RETURNS nvarchar(max)
CREATE FUNCTION RegExReplaceWithOptions(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @replacement nvarchar(max),
    @options int) RETURNS nvarchar(max)
CREATE FUNCTION RegExReplaceCount(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @replacement nvarchar(max),
    @count int) RETURNS nvarchar(max)
CREATE FUNCTION RegExReplaceCountWithOptions(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @replacement nvarchar(max),
    @count int,
    @options int) RETURNS nvarchar(max)

String splitting functions

CREATE FUNCTION RegExSplit(
    @input nvarchar(max),
    @pattern nvarchar(max)) RETURNS TABLE (ITEM NVARCHAR(MAX))
CREATE FUNCTION RegExSplitWithOptions(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @options int) RETURNS TABLE (ITEM NVARCHAR(MAX))

Matchting functions returning all matches as a table

CREATE FUNCTION RegExMatches(
    @input nvarchar(max),
    @pattern nvarchar(max)) RETURNS TABLE (ITEM NVARCHAR(MAX))
CREATE FUNCTION RegExMatchesWithOptions(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @options int) RETURNS TABLE (ITEM NVARCHAR(MAX))
CREATE FUNCTION RegExMatchesGroup(
    @input nvarchar(max),  
    @pattern nvarchar(max),
    @group int) RETURNS TABLE (ITEM NVARCHAR(MAX))
CREATE FUNCTION RegExMatchesGroupWithOptions(
    @input nvarchar(max),
    @pattern nvarchar(max),
    @group int,
    @options int) RETURNS TABLE (ITEM NVARCHAR(MAX))

Escape and unescape string functions

CREATE FUNCTION RegExEscape(@input nvarchar(max)) RETURNS NVARCHAR(MAX)
CREATE FUNCTION RegExUnescape(@input nvarchar(max)) RETURNS NVARCHAR(MAX)

Statistic and diagnostics collection functions

CREATE FUNCTION RegExCachedCount() RETURNS INT
CREATE FUNCTION RegExClearCache() RETURNS INT
CREATE FUNCTION RegExExecCount() RETURNS BIGINT
CREATE FUNCTION RegExCacheHitCount() RETURNS BIGINT
CREATE FUNCTION RegExExceptionCount() RETURNS BIGINT
CREATE FUNCTION RegExResetExecCount() RETURNS BIGINT
CREATE FUNCTION RegExResetCacheHitCount() RETURNS BIGINT
CREATE FUNCTION RegExResetExceptionCount() RETURNS BIGINT
CREATE FUNCTION RegExSetCacheEntryExpirationMilliseconds(
    @cacheEntryExpirationMilliseconds int) RETURNS INT
CREATE FUNCTION RegExCacheList() RETURNS TABLE (
    PATTERN NVARCHAR(MAX), 
    OPTIONS INT, 
    CACHEREGEXCOUNT INT,
    TTL INT
)