sql - 从数据库中删除样式 (SQL Server)

Question

我正在处理旧数据库表，其中包含附加了 CSS 的值。

例如:

<font size="25">Select your gender?</font>

<font size="25">Select your country?</font>

除了一次删除一个样式之外，还有什么方法可以一次删除所有样式吗？

我想删除 <font size="25"></font>并保持选择你的性别

提前致谢!

Answer 1

借助辅助函数

示例

Declare @YourTable table (SomeCol varchar(max) )
Insert Into @YourTable values
 ('1.<font size="25">Select your <b>gender</b>?</font>')   -- Notice nested tags <b> ..</b>
,('2.<font size="25">Select your <span style="color:blue;">country</span>?</font>')

Select B.*
 From  @YourTable A
 Cross Apply (
                Select NewStr = Stuff((Select '' +RetVal 
                                         From  [dbo].[tvf-Str-Extract](SomeCol,'>','<') 
                                         Order By RetSeq
                                         For XML Path ('')),1,0,'')
              ) B

返回

NewStr
1.Select your gender?
2.Select your country?

感兴趣的函数

CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = row_number() over (order by 1/0)
                  ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
            From  ( values (convert(xml,'<x>' + replace((Select replace(@String,@Delim1,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>').query('.'))) as A(XMLData)
            Cross Apply XMLData.nodes('x') AS B(i)
          ) C1
    Where charindex(@Delim2,RetVal)>1
)
/*
Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/