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