Use regular expressions in sqlserver

SQL Server is weak in string processing, and the replace function only supports strings as matching items. To use a regular expression, you can only use the system stored procedure to call the VB component, below are two written regular functions:

Test whether match:

Gocreate function DBO. regexismatch (@ source varchar (5000 ), -- Source string to be matched @ Regexp varchar (1000 ), -- Regular Expression @ Ignorecase bit = 0 -- Case sensitive. The default value is false. ) Returns bit -- Return result-false,-true Asbegin -- 0 (successful) or non-zero number (failed) is the integer of the hresult returned by the OLE Automation Object. Declare @ HR integer -- Used to save the returned object token for subsequent operations on the object Declare @ objregexp integer declare @ objmatches integer -- Save the result Declare @ results bit/* Create an OLE object instance. Only members of the SysAdmin fixed server role can execute sp_oacreate and make sure the machine contains the VBScript. Regexp class library */ Exec @ hR = sp_oacreate' VBScript. Regexp ', @ Objregexp outputif @ HR <> 0 beginset @ Results = 0 return @ resultsendexec @ hR = sp_oasetproperty @ objregexp ,' Pattern ', @ Regexpif @ HR <> 0 beginset @ Results = 0 return @ resultsendexec @ hR = sp_oasetproperty @ objregexp ,' Global ', Falseif @ HR <> 0 beginset @ Results = 0 return @ resultsendexec @ hR = sp_oasetproperty @ objregexp ,' Ignorecase ', @ Ignorecaseif @ HR <> 0 beginset @ Results = 0 return @ resultsend -- Call the object Method Exec @ hR = sp_oamethod @ objregexp ,'Test ', @ Results output, @ sourceif @ HR <> 0 beginset @ Results = 0 return @ resultsend -- Release the created OLE object Exec @ hR = sp_oadestroy @ objregexpif @ HR <> 0 beginset @ Results = 0 return @ resultsendreturn @ resultsend

Regular Expression replacement:

Go -- SQL Regular Expression replacement function 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) as 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

 
The key is the use of sp_oacreate, sp_oadestroy, sp_oasetproperty, and sp_oamethod stored procedures. This is also used in the article writing extended stored procedures.
 

 
Test the replacement function: Print DBO. regexreplace ('sfasdf234sdfds ',' \ d', '', 1, 0)
 

 
You must enable the use of the OLE stored procedure during use:
 

 
Exec sp_configure'OLE Automation procedures', 1 reconfigure
 

 
When setting this configuration, the system may prompt "the configuration option 'ole automation procedures 'does not exist, or it may be an advanced option ." The solution is to execute the statement before Configuration:
 

 
Exec sp_configure'Show advanced options', 1 reconfigure