The code for the SQL Regular Expression replacement function is as follows: CREATEfunctiondbo. regexReplace (@ sourcentext, -- original string @ regexpvarchar (1000), -- Regular Expression @ replacevarchar (1000), -- replace value @ globalReplacebit1, -- whether to globally replace @ ignoreCasebit0 -- whether to ignore case sensitivity) r
-- SQL Regular Expression replacement function code: CREATE function dbo. regexReplace (@ source ntext, -- original string @ regexp varchar (1000), -- Regular Expression @ replace varchar (1000), -- replace value @ globalReplace bit = 1, -- whether to globally replace @ ignoreCase bit = 0 -- whether to ignore case sensitivity) r
-- SQL Regular Expression replacement function
The Code is as follows:
CREATE function dbo. regexReplace
(
@ Source ntext, -- original string
@ Regexp varchar (1000), -- Regular Expression
@ Replace varchar (1000), -- replace value
@ GlobalReplace bit = 1, -- whether it is a global replacement
@ IgnoreCase bit = 0 -- ignore case sensitivity
)
ReturnS varchar (1000)
Begin
Declare @ hr integer
Declare @ objRegExp integer
Declare @ result varchar (5000)
Exec @ hr = sp_OACreate 'vbscript. RegExp ', @ objRegExp OUTPUT
IF @ hr <> 0 begin
Exec @ hr = sp_OADestroy @ objRegExp
Return null
End
Exec @ hr = sp_OASetProperty @ objRegExp, 'pattern', @ regexp
IF @ hr <> 0 begin
Exec @ hr = sp_OADestroy @ objRegExp
Return null
End
Exec @ hr = sp_OASetProperty @ objRegExp, 'global', @ globalReplace
IF @ hr <> 0 begin
Exec @ hr = sp_OADestroy @ objRegExp
Return null
End
Exec @ hr = sp_OASetProperty @ objRegExp, 'ignorecase ', @ IgnoreCase
IF @ hr <> 0 begin
Exec @ hr = sp_OADestroy @ objRegExp
Return null
End
Exec @ hr = sp_OAMethod @ objRegExp, 'replace ', @ result OUTPUT, @ source, @ Replace
IF @ hr <> 0 begin
Exec @ hr = sp_OADestroy @ objRegExp
Return null
End
Exec @ hr = sp_OADestroy @ objRegExp
IF @ hr <> 0 begin
Return null
End
Return @ result
End
/*
Configure support for Extended Stored Procedures
Microsoft SQL Server 2005-> Configuration tool-> peripheral application configurator-> peripheral application configuration of the function-> Ole Automation: supports Ole Automation
Example 1:
The Code is as follows:
Declare @ source nvarchar (4000)
Set @ source = 'dsafsdf'
Select dbo. regexReplace (@ source, '<[^>] +>', '', 1, 1)
Example 2: (replace aaa with aaa in the database field)
Select id, dbo. regexReplace (field, ']) *>', '', 1, 0) AS Alias From table
*/
,