CreateAssemblyTemplate.sql

/* Source code for the library at: https://github.com/Ascentis/mssql-regex */

PRINT 'Checking if regex package already exists...';

DECLARE @clrName nvarchar(4000) = 'Ascentis.RegExSql';
DECLARE @asmBin varbinary(max) = <BinaryDll>;
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES('SHA2_512', @asmBin);

IF NOT EXISTS (select *
               from sys.assembly_files f
               where HASHBYTES('SHA2_512', f.content) = @hash
                     AND f.name = @clrName)
BEGIN	
    PRINT 'Dropping regex functions';
    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExIsMatch]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExIsMatch;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExIsMatchWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExIsMatchWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatch]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatch;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchIndexed]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchIndexed;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchIndexedWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchIndexedWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExSplit]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExSplit;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExSplitWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExSplitWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExReplace]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExReplace;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExReplaceWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExReplaceWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExReplaceCount]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExReplaceCount;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExReplaceCountWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExReplaceCountWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExEscape]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExEscape;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExUnescape]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExUnescape;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatches]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatches;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchesWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchesWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchGroup]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchGroup;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchGroupWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchGroupWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchGroupIndexed]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchGroupIndexed;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchGroupIndexedWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchGroupIndexedWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchesGroup]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchesGroup;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchesGroupWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchesGroupWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchesGroups]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchesGroups;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExMatchesGroupsWithOptions]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExMatchesGroupsWithOptions;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExCachedCount]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExCachedCount;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExCacheHitCount]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExCacheHitCount;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExClearCache]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExClearCache;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExExecCount]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExExecCount;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExExceptionCount]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExExceptionCount;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExResetExecCount]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExResetExecCount;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExResetCacheHitCount]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExResetCacheHitCount;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExResetExceptionCount]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExResetExceptionCount;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExSetCacheEntryExpirationMilliseconds]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExSetCacheEntryExpirationMilliseconds;

    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id = OBJECT_ID(N'[dbo].[RegExCacheList]')
                      AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
        DROP FUNCTION RegExCacheList;

    PRINT 'Dropping existing assemblies matching name "Ascentis.RegExQL"'
    IF EXISTS (select *
        from sys.assembly_files f
        full outer join  sys.assemblies a
            on f.assembly_id=a.assembly_id
        where a.name ='Ascentis.RegExSQL')
        DROP ASSEMBLY [Ascentis.RegExSQL];

    PRINT 'Resetting CLR Security to "Enabled"'
    IF EXISTS(SELECT *
              from sys.configurations	
              where name = 'clr strict security' and value = 0)
        EXEC SP_CONFIGURE 'clr strict security', 1;

    RECONFIGURE;

    PRINT 'Adding our assembly to list of trusted assemblies'
    IF EXISTS(SELECT * from sys.trusted_assemblies
              WHERE description = 'Ascentis.RegExSql')
    BEGIN
        DECLARE @UnTrustAssembliesCmd nvarchar(max) = '';

        SELECT @UnTrustAssembliesCmd = @UnTrustAssembliesCmd + 'EXEC sys.sp_drop_trusted_assembly @hash = ' + CONVERT(varchar(max), hash, 1) + ';' 
        FROM sys.trusted_assemblies
        WHERE description = 'Ascentis.RegExSql';

        EXEC sp_executesql @UnTrustAssembliesCmd;
    END;

    EXEC sys.sp_add_trusted_assembly @hash = @hash, @description = @clrName;

    PRINT 'Pushing our assembly into the database';
    CREATE ASSEMBLY [Ascentis.RegExSQL]
    FROM @asmBin
    WITH PERMISSION_SET = UNSAFE;

    PRINT 'Creating function stubs';
    DECLARE @CreateFnCommand nvarchar(max) = '';

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExIsMatch( 
          @input nvarchar(max),
          @pattern nvarchar(max)
        )
        RETURNS bit EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledIsMatch;';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExIsMatchWithOptions( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @options int
        )
        RETURNS bit EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledIsMatchWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatch( 
          @input nvarchar(max),
          @pattern nvarchar(max)  
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatch'
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchWithOptions( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @options int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchWithOptions'
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchIndexed( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @index int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchIndexed';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchIndexedWithOptions( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @index int,
          @options int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchIndexedWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchGroup( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @group int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchGroup';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchGroupWithOptions( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @group int,
          @options int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchGroupWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchGroupIndexed( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @group int,
          @index int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchGroupIndexed';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchGroupIndexedWithOptions( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @group int,
          @index int,
          @options int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchGroupIndexedWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExReplace( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @replacement nvarchar(max)
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledReplace';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExReplaceWithOptions( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @replacement nvarchar(max),
          @options int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledReplaceWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExReplaceCount( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @replacement nvarchar(max),
          @count int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledReplaceCount';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExReplaceCountWithOptions( 
          @input nvarchar(max),
          @pattern nvarchar(max),
          @replacement nvarchar(max),
          @count int,
          @options int
        )
        RETURNS nvarchar(max) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledReplaceCountWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExSplit(
            @input nvarchar(max),
            @pattern nvarchar(max)
        )
        RETURNS TABLE (ITEM NVARCHAR(MAX)) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledSplit';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExSplitWithOptions(
            @input nvarchar(max),
            @pattern nvarchar(max),
            @options int
        )
        RETURNS TABLE (ITEM NVARCHAR(MAX)) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledSplitWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExEscape(
            @input nvarchar(max)
        )
        RETURNS NVARCHAR(MAX) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledEscape';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExUnescape(
            @input nvarchar(max)
        )
        RETURNS NVARCHAR(MAX) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledUnescape';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatches(
            @input nvarchar(max),
            @pattern nvarchar(max)
        )
        RETURNS TABLE (ITEM NVARCHAR(MAX)) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatches';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchesWithOptions(
            @input nvarchar(max),
            @pattern nvarchar(max),
            @options int
        )
        RETURNS TABLE (ITEM NVARCHAR(MAX)) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchesWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchesGroup(
            @input nvarchar(max),
            @pattern nvarchar(max),
            @group int
        )
        RETURNS TABLE (ITEM NVARCHAR(MAX)) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchesGroup';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchesGroupWithOptions(
            @input nvarchar(max),
            @pattern nvarchar(max),
            @group int,
            @options int
        )
        RETURNS TABLE (ITEM NVARCHAR(MAX)) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchesGroupWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchesGroupsWithOptions(
            @input nvarchar(max),
            @pattern nvarchar(max),      
            @options int
        )
        RETURNS TABLE (
            MatchNum int,
            GrpNum int,
            GrpName nvarchar(255),
            Item NVARCHAR(MAX)) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchesGroupsWithOptions';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExMatchesGroups(
            @input nvarchar(max),
            @pattern nvarchar(max)            
        )
        RETURNS TABLE (
            MatchNum int,
            GrpNum int,
            GrpName nvarchar(255),
            Item NVARCHAR(MAX)) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCompiledMatchesGroups';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExCachedCount()
        RETURNS INT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCachedCount';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExClearCache()
        RETURNS INT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExClearCache';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExExecCount()
        RETURNS BIGINT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExExecCount';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExCacheHitCount()
        RETURNS BIGINT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCacheHitCount';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExExceptionCount()
        RETURNS BIGINT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExExceptionCount';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExResetExecCount()
        RETURNS BIGINT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExResetExecCount';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExResetCacheHitCount()
        RETURNS BIGINT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExResetCacheHitCount';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExResetExceptionCount()
        RETURNS BIGINT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExResetExceptionCount';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExSetCacheEntryExpirationMilliseconds(
            @cacheEntryExpirationMilliseconds int
        )
        RETURNS INT EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExSetCacheEntryExpirationMilliseconds';
    EXEC sp_executesql @CreateFnCommand;

    SET @CreateFnCommand = '
        CREATE FUNCTION RegExCacheList()
        RETURNS TABLE (
            PATTERN NVARCHAR(MAX), 
            OPTIONS INT, 
            CACHEREGEXCOUNT INT,
            TTL INT) EXTERNAL NAME [Ascentis.RegExSQL].RegExCompiled.RegExCacheList';
    EXEC sp_executesql @CreateFnCommand;    
END
ELSE
BEGIN
    PRINT 'Assembly already exists. Exiting';
END
GO

PRINT 'Testing our regex library...'
IF NOT EXISTS(SELECT * 
              FROM dbo.RegExMatches('SM1,M 29,B 13', '(^|,)(E372|M 29|E275|B 13)((?=,)|(?=$))'))
BEGIN
    THROW 50001, 'Something is wrong with regex library. Expected matches calling RegExMatches(). Test failed', 1;
END
PRINT 'Test passed';