T-SQL SQL Server 2014 中的 HTML 转义 [英] HTML Escape in T-SQL SQL Server 2014
问题描述
我在 SQL Server 数据库中有一个列，它以以下方式存储文本块:
I have a column in a SQL Server database that stores a text block in the following fashion:
<HTML><HEAD><style type="text/css">BODY,TD,TH,BUTTON,INPUT,SELECT,TEXTAREA{FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial,Helvetica;}BODY{MARGIN: 5px;}P,DIV,UL,OL,BLOCKQUOTE{MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px;}</style></HEAD><BODY> <p style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px">Patient is a 84 year old female. Patient's histpry includes the following:</p> <p style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px"> </p></BODY></HTML>我想从上面这个特定的例子中带回来的是:
All I want to bring back from this particular example above would be:
Patient is an 84 year old female. Patient's histpry includes the following:
老实说，我什至不知道从哪里开始，SQL Server 2014 中是否有任何 HTML 转义类型函数?我无权访问 CLI，我需要在我负责创建的存储过程中运行代码.
I honestly do not even know where to start, is there any HTML escape type functions in SQL Server 2014? I do not have access to CLI and I will need to run the code inside of a stored procedure that I have been tasked with creating.
推荐答案
如果对表值函数开放，请考虑以下事项.
If open to a Table-Valued Function, consider the following.
厌倦了提取字符串(左、右、charindex、patindex、reverse 等)，我修改了 split/parse 函数以接受两个不同的分隔符.在这种情况下 > 和
Tired of extracting strings (left, right, charindex, patindex, reverse, etc), I modified a split/parse function to accept two non-like delimiters. In this case > and </
此外，作为 TVF，如果您的数据在表格中，则很容易合并到 CROSS APPLY 中.
Also, being a TVF, it is easy to incorporate into a CROSS APPLY if you data is in a table.
示例
Declare @S varchar(max)='<HTML><HEAD><style type="text/css">BODY,TD,TH,BUTTON,INPUT,SELECT,TEXTAREA{FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial,Helvetica;}BODY{MARGIN: 5px;}P,DIV,UL,OL,BLOCKQUOTE{MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px;}</style></HEAD><BODY> <p style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px">Patient is a 84 year old female. Patient''s histpry includes the following:</p> <p style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px"> </p></BODY></HTML>'
Select *
From [dbo].[tvf-Str-Extract](replace(@S,' ',' '),'>','</')
Where RetVal<>' '
and RetVal not like 'BODY,%'
退货
RetSeq RetPos RetVal
2 284 Patient is a 84 year old female. Patient's histpry includes the following:
注意: WHERE 是可选的，可能需要进行调整以满足您的实际需要.只是为了好玩，在没有 WHERE 的情况下尝试一下.此外，在此示例中，我们捕获了 ，但如您所知，可能还有许多其他，即 —.
Note: The WHERE is optional and may have to be tweaked to suite you actual needs. Just for fun, try it without the WHERE. Also, in this example, we trapped the , but as you know, there may be many others i.e. —.
感兴趣的功能
CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table
As
Return (
with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By N)
,RetPos = N
,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1)
From (
Select *,RetVal = Substring(@String, N, L)
From cte4
) A
Where charindex(@Delimiter2,RetVal)>1
)
/*
Max Length of String 1MM characters
Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/
这篇关于T-SQL SQL Server 2014 中的 HTML 转义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
