An implementation of SQL Server regular substitution function

--function

IF object_id(N'dbo. Regexreplace') is  not NULL DROP FUNCTIONdbo. RegexreplaceGO CREATE FUNCTIONdbo. Regexreplace (@string VARCHAR(MAX),--the string to be replaced@pattern VARCHAR(255),--Replace template@replacestr VARCHAR(255),--the replaced string@IgnoreCase INT = 0 --0 Case-sensitive 1 is case insensitive) RETURNS VARCHAR(8000)  as BEGIN DECLARE @objRegex INT,@retstr VARCHAR(8000) --Creating ObjectsEXECsp_OACreate'VBScript.RegExp',@objRegex out--Setting PropertiesEXECsp_OASetProperty@objRegex,'Pattern',@pattern EXECsp_OASetProperty@objRegex,'IgnoreCase',@IgnoreCase EXECsp_OASetProperty@objRegex,'Global',1 --ExecutionEXECsp_OAMethod@objRegex,'Replace',@retstrOut,@string,@replacestr --ReleaseEXECUTEsp_OADestroy@objRegex RETURN @retstr END GO

Usage example (replace space):

-- Replace spaces print dbo. Regexreplace ('0   _ 276 _ 5'\s+", 1 )-- output: 0_276_5

An implementation of SQL Server regular substitution function