SQL Server uses regular expressions to replace strings.

SQL Server uses regular expressions to replace strings.

Create a regular expression replacement function and use the OLE object. The following is the function code:

-- IF yes, delete the original function IF OBJECT_ID (n' dbo. regexReplace ') is not null drop function dbo. regexReplace GO -- start to CREATE the regular expression replacement function create function dbo. regexReplace (@ string VARCHAR (MAX), -- replaced string @ pattern VARCHAR (255), -- replaced template @ replacestr VARCHAR (255 ), -- replace the string @ IgnoreCase INT = 0 -- 0 case sensitive 1 case insensitive) returns varchar (8000) as begin declare @ objRegex INT, @ retstr VARCHAR (8000) -- create the EXEC sp_OACreate 'vbscript. regExp ', @ timed OUT -- set the property EXEC sp_OASetProperty @ objRegex, 'pattern', @ Pattern EXEC sp_OASetProperty @ override, 'ignorecase', @ IgnoreCase EXEC sp_OASetProperty @ objRegex, 'global ', 1 -- execute exec sp_OAMethod @ objRegex, 'replace ', @ retstr OUT, @ string, @ replacestr -- release EXECUTE sp_OADestroy @ objRegex RETURN @ retstr end go -- to ensure normal operation, you need to set the Ole Automation Procedures option to 1 EXEC sp_configure 'show advanced options', 1 reconfigure with override exec sp_configure 'ole Automation Procedures ', 1 RECONFIGURE WITH OVERRIDE

-- 2. reversely SELECT dbo. regexReplace ('John Smith ',' ([a-z] +) \ s ([a-z] +) ',' $2, $ 1', 1) /* -------------------------------------- Smith, John */--------------------------------------------------

Only those who are enthusiastic about writing programs can write good programs!

The above section describes how to replace strings with regular expressions in SQL Server. I hope this will be helpful to you, if you have any questions, please leave a message and I will reply to you in time!