SQL Regular Expression replacement

Document directory

• Example1: case insensitive and replaced
• Example2: Use greedy match
• Example3: Remove HTML tags
• Example4: database Field Value Replacement

Replace SQL regular expressions with comments from January 1,-by keepfool1

Now there is a small scenario where a field in a table in the database stores HTML code. If you need to replace all the <A> labels in HTML code.

Of course we can do this in C:

Regex regex = new Regex(@"<a[^>]*>[^<]*</a>");string cleanedHtml = regex.Replace(html, "");

However, I don't want to write another loop to traverse each record, and then save each record. I want to put every step in the database, while SQL only provides a simple replace function, this function obviously cannot meet our requirements. Write a UDF.

The function source code is as follows:

-- 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 to globally replace @ ignorecase bit = 0 -- whether to ignore case sensitivity) returns varchar (1000) asbegindeclare @ HR integerdeclare @ objregexp integerdeclare @ result varchar (5000) exec @ hR = sp_oacreate. regexp ', @ objregexp outputif @ HR <> 0 beginexec @ hR = sp_oadestroy @ objregexpreturn nullendexec @ hR = sp_oasetproperty @ objregexp, 'pattern ', @ regexpif @ HR <> 0 beginexec @ hR = sp_oadestroy @ objregexpreturn nullendexec @ hR = sp_oasetproperty @ objregexp, 'global ', @ globalreplaceif @ HR <> 0 beginexec @ hR = sp_oadestroy @ objregexpreturn nullendexec @ hR = sp_oasetproperty @ objregexp, 'ignorecase ', @ ignorecaseif @ HR <> 0 beginexec @ hR = sp_oadestroy @ objregexpreturn nullendexec @ hR = sp_oamethod @ objregexp, 'replace ', @ result output, @ source, @ replaceif @ HR <> 0 beginexec @ hR = sp_oadestroy @ objregexpreturn nullendexec @ hR = sp_oadestroy @ objregexpif @ HR <> 0 beginreturn nullend return @ resultend

It should be noted that this function cannot be used immediately even after it is written. The following error may occur when executing this function:

Msg 15281, Level 16, State 1, Line 1SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

This is because it is not enabledOLE Automation proceduresThe OLE Automation procedures option in msdn. Run the following statement to enable this option:

sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Ole Automation Procedures', 1;GORECONFIGURE;GO

All preparations have been completed, so let's test it.

Example1: case insensitive and replaced

select dbo.regexReplace('<A HREF="www.jileiba.com" target="_blank" style="color:red;">123456</a>','<a[^>]*>[^<]*</a>','',1,1)

Example2: Use greedy match

HTML code:

<p>    Also Available - <a style="text-decoration: none" href="/isbn/9780199218691"><font        color="#000FF"><b>Smith & Hogan: Criminal Law Cases & Materials 10th ed</b></font></a>    <p>        There is, as ever, detailed analysis of the many recent case developments, in particular,        a revision of the chapter dealing with secondary liability and joint enterprise.</p></p>

Call code:

select dbo.regexReplace(html,'<a[^>]*>(.|\n)*?</a>','',1,1)

Example3: Remove HTML tags

select dbo.regexReplace('<p><b>Key Contact:</b><br> Mr  Jack,  Zhou<br> General Manager<br> <p> Mr  Adu,  Ho<br> Marketing Director<br> Overseas Sales<br> <p> Ms  Winny,  Luo<br> Sales Manager<br> Overseas Sales<br> <p>','<[^>]*>','',1,0) 

Example4: database Field Value Replacement

update Booksset [Description] = dbo.regexReplace([Description],'<a[^>]*>(.|\n)*?</a>','',1,1)