SQL - 删除字符串中的所有HTML标记 [英] SQL - Remove all HTML tags in a string
问题描述
在我的数据集中,我有一个存储用HTML标记的文本的字段。一般格式如下: $ b
< html>< head>< / head>< body>< p>我的
< / html>
我可以尝试通过执行以下操作来解决问题:
REPLACE(REPLACE(Table.HtmlData,'< html>< head>< / head>< body> ;< p>',''),'< / p>< / body>< / html>')
然而,这不是一个严格的规则,因为一些条目违反了W3C标准,并且不包含< head>
标签。更糟的是,可能缺少结束标记。所以我需要为每个可能存在的开始和结束标记包含 REPLACE
函数。
REPLACE REPLACE REPLACE REPLACE REPLACE(REPLACE(
Table.HtmlData,
'< html>',''),
' < / html>',''),
'< head>,''),
'< / head>',''),
'< body> ''),
'< / body>',''),
'< p>',''),
'< / p>' '')
我想知道是否有更好的方法来实现这个比使用多重嵌套 REPLACE
函数。不幸的是,我在这个环境中可用的唯一语言是SQL和Visual Basic(不是.NET)。
DECLARE @x XML ='< html>< head>< / head>< body>< p>我的文字< / p>< / body>< / html> ;'
SELECT tcvalue('。','NVARCHAR(MAX)')
FROM @ x.nodes('*')t(c)
更新 - 对于未封闭标签的字符串:
DECLARE @x NVARCHAR(MAX)='< html>< head>< / head>< body>< p>我的文字。 ; / body>< / html>'
SELECT x.value('。','NVARCHAR(MAX)')
FROM(
SELECT x = CAST(REPLACE (REPLACE(@x,'>','/>'),'< /','<')AS XML)
)r
In my dataset, I have a field which stores text marked up with HTML. The general format is as follows:
<html><head></head><body><p>My text.</p></body></html>
I could attempt to solve the problem by doing the following:
REPLACE(REPLACE(Table.HtmlData, '<html><head></head><body><p>', ''), '</p></body></html>')
However, this is not a strict rule as some of entries break W3C Standards and do not include <head>
tags for example. Even worse, there could be missing closing tags. So I would need to include the REPLACE
function for each opening and closing tag that could exist.
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
Table.HtmlData,
'<html>', ''),
'</html>', ''),
'<head>', ''),
'</head>', ''),
'<body>', ''),
'</body>', ''),
'<p>', ''),
'</p>', '')
I was wondering if there was a better way to accomplish this than using multiple nested REPLACE
functions. Unfortunately, the only languages I have available in this environment are SQL and Visual Basic (not .NET).
DECLARE @x XML = '<html><head></head><body><p>My text.</p></body></html>'
SELECT t.c.value('.', 'NVARCHAR(MAX)')
FROM @x.nodes('*') t(c)
Update - For strings with unclosed tags:
DECLARE @x NVARCHAR(MAX) = '<html><head></head><body><p>My text.<br>More text.</p></body></html>'
SELECT x.value('.', 'NVARCHAR(MAX)')
FROM (
SELECT x = CAST(REPLACE(REPLACE(@x, '>', '/>'), '</', '<') AS XML)
) r
这篇关于SQL - 删除字符串中的所有HTML标记的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!