問題描述

我在 SQL Server 2005 中有一個表，其中包含數百行 HTML 內容.某些內容具有 HTML，例如:

I have a table in SQL Server 2005 with hundreds of rows with HTML content. Some of the content has HTML like:

<span class=heading-2>Directions</span>

方向"隨頁面名稱而變化.

where "Directions" changes depending on page name.

我需要將所有 和 標簽更改為

> 和

I need to change all the <span class=heading-2> and </span> tags to <h2> and </h2> tags.

我過去編寫此查詢是為了進行內容更改，但由于結束 HTML 標記，它不適用于我當前的問題:

I wrote this query to do content changes in the past, but it doesn't work for my current problem because of the ending HTML tag:

Update  ContentManager
Set ContentManager.Content = replace(Cast(ContentManager.Content AS NVARCHAR(Max)), 'old text', 'new text')

有誰知道我怎樣才能完全在 T-SQL 中實現到 h2 的替換?我發現的一切都表明我必須進行 CLR 集成.謝謝！

Does anyone know how I could accomplish the span to h2 replacing purely in T-SQL? Everything I found showed I would have to do CLR integration. Thanks!

推薦答案

確實，T-SQL 本身并不支持正則表達式，在這種問題中，正則表達式將成為首選工具.首先，我會說解決方案的復雜程度在很大程度上取決于您的數據的一致性.例如，假設我們搜索具有以下標題的項目:

Indeed T-SQL does not natively support regular expressions and this is the sort of problem in which regular expressions would be the tool of choice. First, I'll say that the level of complication in the solution depends greatly on how consistent your data is. For example, suppose we search for items with the heading:

Select ..
From ...
Where HtmlContent Like '<span class="heading-2">%'

這假定 span 和 class 之間沒有額外的間距，并且在結束括號之前的最后一個雙引號之后沒有額外的間距.我們可以編寫 '%<span%class="heading-2"%>%' 來說明空格，但也會發現 div 標記為 heading-2 在與任何 span 標簽相同的內容中.如果后面的場景不應該發生，但你可能有不同的空間，那么使用這個修改后的模式.我們真正會遇到麻煩的是結束標簽.假設我們的內容如下所示:

This assumes no additional spacing between span and class as well as no additional spacing after the final double quote before the end bracket. We could write '%<span%class="heading-2"%>%' to account for the spaces but that would also find div tags marked as heading-2 in the same content as any span tag. If this later scenario shouldn't happen but you might have varying spaces, then use this revised pattern. Where we will really run into troubles is the closing tag. Suppose our content looks like so:

<span class="heading-2"> Foo <span class="heading-3">Bar</span> And Gamma Too</span> .... <span class="heading-4">Fubar Is the right way!</span>...

找到正確的結束 span 標記以更改為 </h2> 并不是那么簡單.您不能簡單地找到第一個 并將其更改為 .如果你知道你沒有嵌套的 span 標簽，那么你可以編寫一個用戶定義的函數來做到這一點:

It is not so simple to find the correct closing span tag to change to </h2>. You cannot simply find the first </span> and change it to </h2>. If you knew that you had no nested span tags, then you could write a user-defined function that would do it:

Create Function ReplaceSpanToH2( @HtmlContent nvarchar(max) )
Returns nvarchar(max)
As
Begin
    Declare @StartPos int
    Declare @EndBracket int

    Set @StartPos = CharIndex('<span class="heading-2">', @HtmlContent)
    If @StartPos = 0
        Return @HtmlContent

    Set @HtmlContent = Replace(@HtmlContent, '<span class="heading-2">', '<h2>')

    -- find next </span>
    Set @StartPos = CharIndex('</span>', @HtmlContent, @StartPos)

    Set @HtmlContent = Stuff(@HtmlContent, @StartPos, 7, '</h2>')
    Return @HtmlContent
End

這篇關于如何在 SQL Server 中替換正則表達式 HTML 標簽?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！