SQL Server: Regular expression functions

However, it is possible to use OLE automation along with the Microsoft VBScript Regular Expressions COM library within user defined functions with which it is possible to create functions that handle regular expressions:

Enabling OLE automation

In order to execute these functions, we need to enable OLE automation (with sp_configure).

Without enabling it, SQL Server throws the error message SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedure' because this component is turned off as part of the security configuration for this server. …

sp_configure 'show advanced options'    , 1;
go

reconfigure;
go

sp_configure 'Ole Automation Procedures', 1;
go

reconfigure;
go

sp_configure 'show advanced options'    , 0;
go

reconfigure;
go

Github repository SQL-Server-helpers, path: /regexp/enable-OLE.sql

regexp_replace

Then, we can create a regexp_replace(…) function:

-- released under the MIT License

drop   function if exists dbo.regexp_replace;
go

create function dbo.regexp_replace (
    @searchstring  varchar(4000),
    @pattern       varchar(4000),
    @replacestring varchar(4000) 
)
returns varchar(4000)
as
begin

    declare @objRegexExp int, 
        @objErrorObj     int,
        @strErrorMessage varchar(255),
        @res             int,
        @result          varchar(4000)

    if @searchstring is null or len(ltrim(rtrim(@searchstring))) = 0 begin
       return null
    end;

    set @result='';

    exec @res = sp_OACreate 'VBScript.RegExp', @objRegexExp out;

    if @res <> 0 begin
       return 'VBScript did not initialize!';
    end;

    exec @res=sp_OASetProperty @objRegexExp, 'Pattern', @pattern;

    if @res <> 0 begin
       return 'Pattern property set failed!';
    end;

    exec @res=sp_OASetProperty @objRegexExp, 'IgnoreCase', 0;

    if @res <> 0 begin
       return 'IgnoreCase option failed!';
    end;

    exec @res=sp_OAMethod @objRegexExp, 'Replace', @result out, @searchstring, @replacestring;

    if @res <> 0 begin
       return 'Bad search string!';
    end;

    exec @res=sp_OADestroy @objRegexExp;

    return @result
end;
go

Github repository SQL-Server-helpers, path: /regexp/replace.sql

The following example shows regexp_replace in action. The parentheses capture words that that then can be reused in the replace string ($n):

select dbo.regexp_replace(
           'foo bar baz',
           '([^\s]*)\s*([^\s]*)\s*([^\s]*)',
           'first word: $1, second word: 2: $2, third word: $3, dollar sign: $$.'
       );
--
-- first word: foo, second word: 2: bar, third word: baz, dollar sign: $.

Github repository SQL-Server-helpers, path: /regexp/example_regexp_replace.sql

regexp_like

-- released under the MIT License

drop   function if exists dbo.regexp_like;
go

create function dbo.regexp_like (
 --
 -- Returns 1 if matched, 0 otherwise
 --
    @sourceString varchar(4000),
    @pattern      varchar(4000)
)
returns int
as
begin

    declare
        @objRegexExp int, 
        @res         int,
        @result      int;

    if @sourceString is null /* or len(ltrim(rtrim(@sourceString))) = 0 */ begin
       return null;
    end;

    exec @res = sp_OACreate 'VBScript.RegExp', @objRegexExp out;

    if @res <> 0 begin
       return 'VBScript did not initialize!';
    end;

    exec @res=sp_OASetProperty @objRegexExp, 'Pattern', @pattern;

    if @res <> 0 begin
       return 'Pattern property set failed!';
    end;

    exec @res=sp_OASetProperty @objRegexExp, 'IgnoreCase', 0;

    if @res <> 0 begin
       return 'IgnoreCase option failed!';
    end;

    exec @res=sp_OAMethod @objRegexExp, 'Test', @result out, @sourceString;

    if @res <> 0 begin
       return 'Calling Test failed';
    end;

    exec @res=sp_OADestroy @objRegexExp;

    return @result
end;
go

Github repository SQL-Server-helpers, path: /regexp/like.sql

regexp_substr

-- released under the MIT License

drop   function if exists dbo.regexp_substr;
go

create function dbo.regexp_substr ( -- {
    @searchstring  varchar(4000),
    @pattern       varchar(4000),
    @occurence     integer
)
returns varchar(4000)
as
begin

    declare @objRegexExp int,
        @objErrorObj     int,
        @strErrorMessage varchar(255),
        @res             int,
        @mtcColl         int,
        @result          varchar(4000),
        @item varchar(100) = 'item(' + str(@occurence - 1) + ').value';

    if @searchstring is null or len(ltrim(rtrim(@searchstring))) = 0 begin
       return null
    end;

    set @result='';

    exec @res=sp_oaCreate 'VBScript.RegExp', @objRegexExp out;
    exec @res=sp_oaSetProperty @objRegexExp, 'Pattern'   , @pattern;
    exec @res=sp_oaSetProperty @objRegexExp, 'ignoreCase', 0;
    exec @res=sp_oaSetProperty @objRegexExp, 'global'    , 1;
    exec @res=sp_oaMethod      @objRegexExp, 'execute'   , @mtcColl out, @searchstring;
    exec @res=sp_oaGetProperty @mtcColl, @item, @result out;
    exec @res=sp_oaDestroy     @mtcColl;
    exec @res=sp_oaDestroy     @objRegexExp;

    return @result
end; -- }
go

Github repository SQL-Server-helpers, path: /regexp/substr.sql

Testing regexp_substr:

declare
   @txt varchar(4000) = 'ninty-nine: 99, fourty-two: 42, eleven: 11',
   @num varchar(4000);

   print(dbo.regexp_substr(@txt, '\d+', 1));
   print(dbo.regexp_substr(@txt, '\d+', 2));
   print(dbo.regexp_substr(@txt, '\d+', 3));
go

SQL Server: Regular expression functions

Enabling OLE automation

regexp_replace

regexp_like

regexp_substr

See also