--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