USE [Test]
GO

/****** Object:  UserDefinedFunction [dbo].[regex_replace]    Script Date: 27.09.2023 13:03:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE function [dbo].[regex_replace] (
--
-- https://stackoverflow.com/a/38462603/180275
--
-- These match exactly the parameters of RegExp
--
    @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, 'Global', 1;
    if @res <> 0 begin
       return 'Global option 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



USE [Test]
GO

/****** Object:  UserDefinedFunction [dbo].[regex_find]    Script Date: 27.09.2023 13:01:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[regex_find]
       (
             @source varchar(5000),
             @regexp varchar(1000),
             @ignorecase bit = 0
       )
       --https://www.sqlteam.com/articles/regular-expressions-in-t-sql
RETURNS bit
AS
       BEGIN
             DECLARE @hr integer
             DECLARE @objRegExp integer
             DECLARE @objMatches integer
             DECLARE @objMatch integer
             DECLARE @count integer
             DECLARE @results bit
             
             EXEC @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
             IF @hr <> 0 BEGIN
                    SET @results = 0
                    RETURN @results
             END
             EXEC @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
             IF @hr <> 0 BEGIN
                    SET @results = 0
                    RETURN @results
             END
             EXEC @hr = sp_OASetProperty @objRegExp, 'Global', false
             IF @hr <> 0 BEGIN
                    SET @results = 0
                    RETURN @results
             END
             EXEC @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignorecase
             IF @hr <> 0 BEGIN
                    SET @results = 0
                    RETURN @results
             END    
             EXEC @hr = sp_OAMethod @objRegExp, 'Test', @results OUTPUT, @source
             IF @hr <> 0 BEGIN
                    SET @results = 0
                    RETURN @results
             END
             EXEC @hr = sp_OADestroy @objRegExp
             IF @hr <> 0 BEGIN
                    SET @results = 0
                    RETURN @results
             END
       RETURN @results
       END
GO